Hej, piszę sobie sql, który ma za zadanie zwrócić ilość pewnych rzeczy w zależności od przedziału czasowego
with finished as (
select
(data ->> 'Duration') :: double precision as duration,
CAST((((data ->> 'startedAt'):: timestamp) AT TIME ZONE 'Europe/Warsaw' ) as date) as date
FROM
operations
where
(data ->> 'Duration') is not null
and (data ->> 'startedAt') :: timestamp >= '2022-02-06T22:53:27Z'
and (data ->> 'finishedAt') :: timestamp <= '2022-05-26T22:53:27Z'
),
under15Minutes as (
select
count(duration),
date
from finished
where duration <= 900000
group by date
),
between15And60Minutes as (
select
count(duration),
date
from finished
where duration > 900000 and duration <= 3600000
group by date
),
between60And120Minutes as (
select
count(duration),
date
from finishedRide
where duration > 3600000 and duration <= 7200000
group by date
),
between120And180Minutes as (
select
count(duration),
date
from finished
where duration > 7200000 and duration <= 10800000
group by date
),
over180Minutes as (
select
count(duration),
date
from finished
where duration > 10800000
group by date
)
select
coalesce(under15Minutes.date, coalesce(between15And60Minutes.date ,coalesce(between120And180Minutes.date, coalesce(between60And120Minutes.date, over180Minutes.date)))) :: text as day,
under15Minutes.count as under15Minutes,
between15And60Minutes.count as between15And60Minutes,
between60And120Minutes.count as between60And120Minutes,
between120And180Minutes.count as between120And180Minutes,
over180Minutes.count as over180Minutes
from under15Minutes
full join between15And60Minutes on under15Minutes.date = between15And60Minutes.date
full join between60And120Minutes on under15Minutes.date = between60And120Minutes.date
full join between120And180Minutes on under15Minutes.date = between120And180Minutes.date
full join over180Minutes on under15Minutes.date = over180Minutes.date
Po wykonaniu zapytania dostaję kilka zduplikowanych dat. Poniżej 2022-05-14 występuje 2 razy.
Próbowałem użyć group by ale nie zadziałało. Problemem jest zapewnie full join, jednak siedzę nad tym kilka h i nie mam pomysłu jak to rozwiązać, aby nie było duplikatów tylko połączone wartości.
Stworzyłem w ramach testów potworka
test1 as (
select
coalesce(under15Minutes.date, between15And60Minutes.date) as date,
under15Minutes.count as under15,
between15And60Minutes.count as between15And60
from under15Minutes
full join between15And60Minutes on under15Minutes.date = between15And60Minutes.date
),
test2 as (
select
coalesce(test1.date, between60And120Minutes.date) as date,
test1.under15 as under15,
test1.between15And60 as between15And60,
between60And120Minutes.count as between60And120
from test1
full join between60And120Minutes on test1.date = between60And120Minutes.date
),
test3 as (
select
coalesce(test2.date, between120And180Minutes.date) as date,
test2.under15 as under15,
test2.between15And60 as between15And60,
test2.between60And120 as between60And120,
between120And180Minutes.count as between120And180
from test2
full join between120And180Minutes on test2.date = between120And180Minutes.date
),
test4 as (
select
coalesce(test3.date, over180Minutes.date) as date,
test3.under15 as under15,
test3.between15And60 as between15And60,
test3.between60And120 as between60And120,
test3.between120And180 as between120And180,
over180Minutes.count as over180
from test3
full join over180Minutes on test3.date = over180Minutes.date
)
select
*
from test4
I on zadziałał poprawnie. Także moje przypuszczenia co do błedu w joinach jest poprawny, ale nie potrafię go naprawić, próbowałem joiny robić po kolei i porównywać z poprzednim joinem, ale to znów powodowało inne duplikaty, również próbowałem użyć joina i 2/3 wartości porównać, ale nie rozwiązuje problemu
- screenshot-20220527215238.png (10 KB) - ściągnięć: 79
- screenshot-20220527224235.png (11 KB) - ściągnięć: 4
SQL 
