Połączenie zduplikowanych wierszy

0

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.
screenshot-20220527215238.png
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-20220527224235.png

1

Cześć,
a może podejść od nieco innej strony... Wygenerować sobie zakres dat i do niego joinować?
Czyli coś w stylu:

with wszystkie_daty as (
----wygenerowanie dat z okresu jaki Cię interesuje -- 
)
---- reszta twojego kodu od tworzenia finished do momentu from---

from wszystkie_daty wd
left join under15Minutes on (wd.dzien = under15Minutes.date)
left join between15And60Minutes on wd.dzien =  between15And60Minutes.date
left join between60And120Minutes on wd.dzien =  between60And120Minutes.date
left join between120And180Minutes on wd.dzien =  between120And180Minutes.date
left join over180Minutes on wd.dzien =  over180Minutes.date

Wtedy nie ma szan, żeby daty się powtórzyły....

0

@areklipno: dzięki nie pomyślałem o tym.

select  distinct 
	finished.date :: text as day,        
        under15Minutes.count as under15,
        between15And60Minutes.count as between15And60,
        between60And120Minutes.count as between60And120, 
        between120And180Minutes.count as between120And180,
        over180Minutes.count as over180
        from finished
        full join under15Minutes on finishedRide.date =  under15Minutes.date
        full join between15And60Minutes on finishedRide.date =  between15And60Minutes.date
        full join between60And120Minutes on finishedRide.date =  between60And120Minutes.date
        full join between120And180Minutes on finishedRide.date =  between120And180Minutes.date
        full join over180Minutes on finishedRide.date =  over180Minutes.date

distinct wrzuciłem bo w finished miałem zduplikowane daty

1 użytkowników online, w tym zalogowanych: 0, gości: 1