ONLINE: http://collabedit.com/6d5yp
Przygotowujemy losowy ciąg kontaktów widzów z telewizją w ciągu 90 minut w dwóch kanałach
Do przygotowania są następujące zapytania
Zapytanie 1
Wylosuj 100 000 wierszy zawierające krotki: minuta [0-89] i kanał [0-1] np.
minuta | kanal
--------------
66 | 0
72 | 0
67 | 0
2 | 1
SELECT FLOOR(random() * 90) AS "minuta", FLOOR(random()* 2) AS "kanal"
FROM generate_series(1, 100000);
Zapytanie 2
Zmodyfikuj poprzednie zapytanie tak, aby między minutą 50 a 70 włącznie losowało jedynie kanał 0
WITH minuty
AS
(
SELECT FLOOR(random() * 90) AS "minuta"
FROM generate_series(1, 100000)
)
SELECT
minuta,
CASE
WHEN minuta BETWEEN 50 AND 70
THEN 0
ELSE FLOOR(random()* 2)
END AS "kanal"
FROM minuty
Zapytanie 3
Wypisz liczby oglądalności w podziale na minuty i kanały, użyj CUBE
minuta | kanal | liczba
-------------------
0 | 0 | 10
1 | 0 | 20
...
89 | 1 | 10
0 | | 20
1 | | 30
....
| 0 | 20000
| 1 | 80000
| | 100000
Rozwiązanie
WITH dane
AS
(
SELECT FLOOR(random() * 90) AS "minuta", FLOOR(random()* 2) AS "kanal"
FROM generate_series(1, 100000)
)
SELECT minuta, kanal, COUNT(*) AS "liczba"
FROM dane
GROUP BY CUBE (kanal, minuta)
ORDER BY kanal, minuta
Zapytanie 4
Dla każdego kwadransa (0 = minuty 0..14, 1 = minuty 15..29) wypisz ile było kontaktów, posortuj po ilości kontaktów i dołącz kolumnę pozycja
kwadrans | kontaktow | pozycja
4 | .... | 1
2 | .... | 2
3 | .... | 3
1 | .... | 4
6 | .... | 5
5 | .... | 6
Rozwiązanie
WITH dane
AS
(
SELECT FLOOR(random() * 90) AS "minuta", FLOOR(random()* 2) AS "kanal"
FROM generate_series(1, 100000)
),
podzielone_na_kwadranse
AS
(
SELECT FLOOR(minuta / 15) AS "kwadrans", kanal
FROM dane
)
SELECT
kwadrans,
COUNT(*) as "liczba",
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC)
FROM podzielone_na_kwadranse
GROUP BY kwadrans
ORDER BY liczba DESC;
Zapytanie 5
Dla każdego kanału, dla każdego kwadransa wypisz trzy minuty z najliczniejszą liczbą kontaktów
kanał | kwadrans | minuta | pozycja
-----------------------------------
0 | 1 | 10 | 1
0 | 1 | 12 | 2
0 | 1 | 4 | 3
0 | 2 | 22 | 1
....
Odpowiedz
WITH dane
AS
(
SELECT FLOOR(random() * 90) AS "minuta", FLOOR(random()* 2) AS "kanal"
FROM generate_series(1, 100000)
),
podzielone_na_kwadranse
AS
(
SELECT
minuta,
FLOOR(minuta / 15) AS "kwadrans",
kanal
FROM dane
),
wszystkie
AS
(
SELECT
kanal,
kwadrans,
minuta,
COUNT(*) AS "liczba",
ROW_NUMBER() OVER(
PARTITION BY kanal, kwadrans
ORDER BY kanal, kwadrans, COUNT(*) DESC, minuta) AS "pozycja"
FROM podzielone_na_kwadranse
GROUP BY kanal, kwadrans, minuta
ORDER BY kanal, kwadrans, COUNT(*) DESC, minuta
)
SELECT *
FROM wszystkie
WHERE pozycja <= 3
Zapytanie 6
Zapisz zagregowane wyniki stu (100) losowań z zadania 2 do tabeli losowania zawierającej dwie kolumny: id losowania (automatycznie ustawiany) i json z losowaniem. Wzór jsona:
{
"0":{
"0":10,
"1":20,
//...
"89":10
},
"1":{
"0":10,
"1":20,
//...
"89":10
}
}
Zapytanie 7
Policz w ilu jsonach na kanale 1 w minucie 20 było więcej kontaktów niż na kanale 0
Zapytanie 8
Policz w ilu jsonach na kanale 1 sumarycznie w minutach 20-30 było więcej kontaktów niż na kanale 0