Random Data Unnest Array Demo, PostgreSQL

Random Data Unnest Array Demo

Language: Layout:

DROP TABLE IF EXISTS RandomData;

CREATE TABLE RandomData (
  "KEY" INT,
  "ID" INT,
  "GROUP" VARCHAR(5),
  "NUM1" DECIMAL(10,2),
  "NUM2" DECIMAL(10,2),
  "BOOL" BOOL,
  "DATE" TIMESTAMP    
);
INSERT INTO RandomData ("KEY") VALUES (1);

WITH RECURSIVE nums AS (
    SELECT r."KEY"
    FROM RandomData r
    UNION ALL
    SELECT n."KEY" + 1 As NEXT_NUM
    FROM nums n
    WHERE n."KEY" < 20
)

INSERT INTO RandomData SELECT * FROM nums WHERE nums."KEY" > 1;

SELECT SETSEED(0.99);
UPDATE RandomData
SET "ID"=FLOOR(RANDOM()*(15-1))+1, "GROUP"=SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', CAST(FLOOR(RANDOM()*(26-1))+1 AS INTEGER), 1),
    "NUM1"=RANDOM(), "NUM2"=RANDOM()*100, "BOOL"=CAST(ROUND(RANDOM()) AS INTEGER)::BOOLEAN,
    "DATE"=TO_TIMESTAMP(RANDOM()*(EXTRACT(EPOCH FROM NOW())-1)+1);

SELECT * FROM RandomData;

SELECT "ID",
       unnest(array['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 
                    'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']) AS myLETTER,
       unnest(array["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", 
                    "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]) AS VALUES
FROM 
  (SELECT "ID",
      SUM(CASE WHEN "GROUP" = 'A' THEN "NUM1" ELSE NULL END) AS "A",
      SUM(CASE WHEN "GROUP" = 'B' THEN "NUM1" ELSE NULL END) AS "B",
      SUM(CASE WHEN "GROUP" = 'C' THEN "NUM1" ELSE NULL END) AS "C",
      SUM(CASE WHEN "GROUP" = 'D' THEN "NUM1" ELSE NULL END) AS "D",
      SUM(CASE WHEN "GROUP" = 'E' THEN "NUM1" ELSE NULL END) AS "E",
      SUM(CASE WHEN "GROUP" = 'F' THEN "NUM1" ELSE NULL END) AS "F",
      SUM(CASE WHEN "GROUP" = 'G' THEN "NUM1" ELSE NULL END) AS "G",
      SUM(CASE WHEN "GROUP" = 'H' THEN "NUM1" ELSE NULL END) AS "H",
      SUM(CASE WHEN "GROUP" = 'I' THEN "NUM1" ELSE NULL END) AS "I",
      SUM(CASE WHEN "GROUP" = 'J' THEN "NUM1" ELSE NULL END) AS "J",
      SUM(CASE WHEN "GROUP" = 'K' THEN "NUM1" ELSE NULL END) AS "K",
      SUM(CASE WHEN "GROUP" = 'L' THEN "NUM1" ELSE NULL END) AS "L",
      SUM(CASE WHEN "GROUP" = 'M' THEN "NUM1" ELSE NULL END) AS "M",
      SUM(CASE WHEN "GROUP" = 'N' THEN "NUM1" ELSE NULL END) AS "N",
      SUM(CASE WHEN "GROUP" = 'O' THEN "NUM1" ELSE NULL END) AS "O",
      SUM(CASE WHEN "GROUP" = 'P' THEN "NUM1" ELSE NULL END) AS "P",
      SUM(CASE WHEN "GROUP" = 'Q' THEN "NUM1" ELSE NULL END) AS "Q",
      SUM(CASE WHEN "GROUP" = 'R' THEN "NUM1" ELSE NULL END) AS "R",
      SUM(CASE WHEN "GROUP" = 'S' THEN "NUM1" ELSE NULL END) AS "S",
      SUM(CASE WHEN "GROUP" = 'T' THEN "NUM1" ELSE NULL END) AS "T",
      SUM(CASE WHEN "GROUP" = 'U' THEN "NUM1" ELSE NULL END) AS "U",
      SUM(CASE WHEN "GROUP" = 'V' THEN "NUM1" ELSE NULL END) AS "V",
      SUM(CASE WHEN "GROUP" = 'W' THEN "NUM1" ELSE NULL END) AS "W",
      SUM(CASE WHEN "GROUP" = 'X' THEN "NUM1" ELSE NULL END) AS "X",
      SUM(CASE WHEN "GROUP" = 'Y' THEN "NUM1" ELSE NULL END) AS "Y",
      SUM(CASE WHEN "GROUP" = 'Z' THEN "NUM1" ELSE NULL END) AS "Z"
  FROM RandomData
  GROUP BY "ID") AS agg;


absolute service time: 0,52 sec

	setseed
1	NULL

	KEY	ID	GROUP	NUM1	NUM2	BOOL	DATE
1	1	3	E	0,7700	53,7900	False	08.06.2014 23:52:09
2	2	1	I	0,5000	73,5900	False	15.02.1993 15:12:44
3	3	1	K	0,9400	25,9100	True	13.01.2000 17:43:14
4	4	13	W	0,7200	41,1500	True	30.01.1982 22:49:46
5	5	5	W	0,1100	8,3600	False	05.05.2002 05:16:46
6	6	8	E	0,8800	3,8300	False	11.08.1987 05:13:59
7	7	2	I	0,2700	30,2000	True	27.05.1998 04:04:41
8	8	1	G	0,0600	12,7800	True	12.05.1997 03:37:47
9	9	6	H	0,4400	90,3000	False	04.11.1974 05:33:55
10	10	5	J	0,7300	21,3700	True	28.01.1996 02:19:12
11	11	13	D	0,2800	34,1600	False	16.11.1985 06:17:25
12	12	13	S	0,7400	87,9100	False	11.09.2001 06:50:05
13	13	14	B	0,8600	40,7100	False	29.07.1975 12:00:56
14	14	14	U	0,9400	6,5900	False	19.07.2016 02:09:41
15	15	2	Q	0,6600	77,7400	False	19.12.1974 19:48:52
16	16	4	Y	0,7500	0,3900	True	23.03.1993 06:19:26
17	17	3	P	0,7100	89,5200	True	09.02.1990 08:48:18
18	18	5	G	0,1500	32,4300	False	03.04.1975 03:13:23
19	19	11	U	0,2500	0,2800	True	10.03.1977 03:59:01
20	20	12	I	0,6100	2,0100	True	22.03.1978 20:54:17

	ID	myletter	values
1	8	A	NULL
2	8	B	NULL
3	8	C	NULL
4	8	D	NULL
5	8	E	0,8800
6	8	F	NULL
7	8	G	NULL
8	8	H	NULL
9	8	I	NULL
10	8	J	NULL
11	8	K	NULL
12	8	L	NULL
13	8	M	NULL
14	8	N	NULL
15	8	O	NULL
16	8	P	NULL
17	8	Q	NULL
18	8	R	NULL
19	8	S	NULL
20	8	T	NULL
21	8	U	NULL
22	8	V	NULL
23	8	W	NULL
24	8	X	NULL
25	8	Y	NULL
26	8	Z	NULL
27	4	A	NULL
28	4	B	NULL
29	4	C	NULL
30	4	D	NULL
31	4	E	NULL
32	4	F	NULL
33	4	G	NULL
34	4	H	NULL
35	4	I	NULL
36	4	J	NULL
37	4	K	NULL
38	4	L	NULL
39	4	M	NULL
40	4	N	NULL
41	4	O	NULL
42	4	P	NULL
43	4	Q	NULL
44	4	R	NULL
45	4	S	NULL
46	4	T	NULL
47	4	U	NULL
48	4	V	NULL
49	4	W	NULL
50	4	X	NULL
51	4	Y	0,7500
52	4	Z	NULL
53	1	A	NULL
54	1	B	NULL
55	1	C	NULL
56	1	D	NULL
57	1	E	NULL
58	1	F	NULL
59	1	G	0,0600
60	1	H	NULL
61	1	I	0,5000
62	1	J	NULL
63	1	K	0,9400
64	1	L	NULL
65	1	M	NULL
66	1	N	NULL
67	1	O	NULL
68	1	P	NULL
69	1	Q	NULL
70	1	R	NULL
71	1	S	NULL
72	1	T	NULL
73	1	U	NULL
74	1	V	NULL
75	1	W	NULL
76	1	X	NULL
77	1	Y	NULL
78	1	Z	NULL
79	13	A	NULL
80	13	B	NULL
81	13	C	NULL
82	13	D	0,2800
83	13	E	NULL
84	13	F	NULL
85	13	G	NULL
86	13	H	NULL
87	13	I	NULL
88	13	J	NULL
89	13	K	NULL
90	13	L	NULL
91	13	M	NULL
92	13	N	NULL
93	13	O	NULL
94	13	P	NULL
95	13	Q	NULL
96	13	R	NULL
97	13	S	0,7400
98	13	T	NULL
99	13	U	NULL
100	13	V	NULL
101	13	W	0,7200
102	13	X	NULL
103	13	Y	NULL
104	13	Z	NULL
105	5	A	NULL
106	5	B	NULL
107	5	C	NULL
108	5	D	NULL
109	5	E	NULL
110	5	F	NULL
111	5	G	0,1500
112	5	H	NULL
113	5	I	NULL
114	5	J	0,7300
115	5	K	NULL
116	5	L	NULL
117	5	M	NULL
118	5	N	NULL
119	5	O	NULL
120	5	P	NULL
121	5	Q	NULL
122	5	R	NULL
123	5	S	NULL
124	5	T	NULL
125	5	U	NULL
126	5	V	NULL
127	5	W	0,1100
128	5	X	NULL
129	5	Y	NULL
130	5	Z	NULL
131	11	A	NULL
132	11	B	NULL
133	11	C	NULL
134	11	D	NULL
135	11	E	NULL
136	11	F	NULL
137	11	G	NULL
138	11	H	NULL
139	11	I	NULL
140	11	J	NULL
141	11	K	NULL
142	11	L	NULL
143	11	M	NULL
144	11	N	NULL
145	11	O	NULL
146	11	P	NULL
147	11	Q	NULL
148	11	R	NULL
149	11	S	NULL
150	11	T	NULL
151	11	U	0,2500
152	11	V	NULL
153	11	W	NULL
154	11	X	NULL
155	11	Y	NULL
156	11	Z	NULL
157	3	A	NULL
158	3	B	NULL
159	3	C	NULL
160	3	D	NULL
161	3	E	0,7700
162	3	F	NULL
163	3	G	NULL
164	3	H	NULL
165	3	I	NULL
166	3	J	NULL
167	3	K	NULL
168	3	L	NULL
169	3	M	NULL
170	3	N	NULL
171	3	O	NULL
172	3	P	0,7100
173	3	Q	NULL
174	3	R	NULL
175	3	S	NULL
176	3	T	NULL
177	3	U	NULL
178	3	V	NULL
179	3	W	NULL
180	3	X	NULL
181	3	Y	NULL
182	3	Z	NULL
183	14	A	NULL
184	14	B	0,8600
185	14	C	NULL
186	14	D	NULL
187	14	E	NULL
188	14	F	NULL
189	14	G	NULL
190	14	H	NULL
191	14	I	NULL
192	14	J	NULL
193	14	K	NULL
194	14	L	NULL
195	14	M	NULL
196	14	N	NULL
197	14	O	NULL
198	14	P	NULL
199	14	Q	NULL
200	14	R	NULL
201	14	S	NULL
202	14	T	NULL
203	14	U	0,9400
204	14	V	NULL
205	14	W	NULL
206	14	X	NULL
207	14	Y	NULL
208	14	Z	NULL
209	12	A	NULL
210	12	B	NULL
211	12	C	NULL
212	12	D	NULL
213	12	E	NULL
214	12	F	NULL
215	12	G	NULL
216	12	H	NULL
217	12	I	0,6100
218	12	J	NULL
219	12	K	NULL
220	12	L	NULL
221	12	M	NULL
222	12	N	NULL
223	12	O	NULL
224	12	P	NULL
225	12	Q	NULL
226	12	R	NULL
227	12	S	NULL
228	12	T	NULL
229	12	U	NULL
230	12	V	NULL
231	12	W	NULL
232	12	X	NULL
233	12	Y	NULL
234	12	Z	NULL
235	6	A	NULL
236	6	B	NULL
237	6	C	NULL
238	6	D	NULL
239	6	E	NULL
240	6	F	NULL
241	6	G	NULL
242	6	H	0,4400
243	6	I	NULL
244	6	J	NULL
245	6	K	NULL
246	6	L	NULL
247	6	M	NULL
248	6	N	NULL
249	6	O	NULL
250	6	P	NULL
251	6	Q	NULL
252	6	R	NULL
253	6	S	NULL
254	6	T	NULL
255	6	U	NULL
256	6	V	NULL
257	6	W	NULL
258	6	X	NULL
259	6	Y	NULL
260	6	Z	NULL
261	2	A	NULL
262	2	B	NULL
263	2	C	NULL
264	2	D	NULL
265	2	E	NULL
266	2	F	NULL
267	2	G	NULL
268	2	H	NULL
269	2	I	0,2700
270	2	J	NULL
271	2	K	NULL
272	2	L	NULL
273	2	M	NULL
274	2	N	NULL
275	2	O	NULL
276	2	P	NULL
277	2	Q	0,6600
278	2	R	NULL
279	2	S	NULL
280	2	T	NULL
281	2	U	NULL
282	2	V	NULL
283	2	W	NULL
284	2	X	NULL
285	2	Y	NULL
286	2	Z	NULL