PostgreSQL - tablas pivotes ejemplos

 
Vista:
Imágen de perfil de outrera

tablas pivotes ejemplos

Publicado por outrera (35 intervenciones) el 16/04/2014 22:59:57
METHOD 1(faster):

1
2
select date,  sum(case when type = 1 then value end) Type1,
sum(case when type = 2 then value end) Type2from yourtablegroup by date


METHOD 2: crosstab

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
select * from crosstab('select date::text, type::text,
         value::numeric(16,2) from tableA 
          where  
               type in (1,2)  and date between ''2012-02-06'' and ''2013-02-13''
	ORDER by 1,2' )
AS ct(date text,type1 numeric(16,2), type2 numeric(16,2))
 
 
 
---Crear extensiones para tablas cruzadas
 
CREATE EXTENSION tablefunc;
 
------------
 
 
CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);
 
INSERT INTO t VALUES ('A', 'Active',   1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active',   4);
INSERT INTO t VALUES ('B', 'Inactive', 5);
INSERT INTO t VALUES ('C', 'Active',   6);
INSERT INTO t VALUES ('C', 'Inactive', 7);
INSERT INTO t VALUES ('C', 'Active',   8);
INSERT INTO t VALUES ('D', 'Inactive', 9);
 
 
 
SELECT row_name AS Section,
       category_1::integer AS Active,
       category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2)
            AS ct (row_name text, category_1 text, category_2 text);
 
 
 
CREATE TABLE emp_sm_contact (
  emp_id BIGINT,
  contact_type VARCHAR(20),
  contact TEXT,
  UNIQUE (emp_id, contact_type)
);
 
 
INSERT INTO emp_sm_contact VALUES (100, 'twitter', 'beking');
INSERT INTO emp_sm_contact VALUES (100, 'linkedIn', 'b.king');
INSERT INTO emp_sm_contact VALUES (100, 'g+', 'bking');
INSERT INTO emp_sm_contact VALUES (101, 'twitter', 'kochhar');
INSERT INTO emp_sm_contact VALUES (101, 'linkedIn', 'kochhar.1');
INSERT INTO emp_sm_contact VALUES (101, 'g+', 'kochhar.2');
INSERT INTO emp_sm_contact VALUES (200, 'twitter', 'whalen');
 
 
 
 
SELECT *
FROM crosstab(
  'SELECT emp_id, contact_type, contact FROM emp_sm_contact ORDER BY 1',
  'SELECT DISTINCT contact_type FROM emp_sm_contact ORDER BY 1'
)
AS emp_sm_contact(emp_id BIGINT, "g+" TEXT, "linkedIn" TEXT, twitter TEXT)
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder