[SQL] Aantal overnachtingen berekenen tijdens één bezoek

[storeman]

Ik zit met een probleem, ik heb een vrij complexe query met verschillende unions etc, maar volgens mij is het probleem vrij simpel. Laten we wat data nemen:

code:

id_city |  date
------------------
1       |  2016-01-01
1       |  2016-01-02
1       |  2016-01-03
2       |  2016-01-04
2       |  2016-01-05
3       |  2016-01-06
1       |  2016-01-07
2       |  2016-01-08
2       |  2016-01-09

Ik wil nu per record berekenen hoeveel nachten men daar verblijft:

code:

id_city |  date        | night_count
----------------------------
1       |  2016-01-01  | 3
1       |  2016-01-02  | 3
1       |  2016-01-03  | 3
2       |  2016-01-04  | 2
2       |  2016-01-05  | 2
3       |  2016-01-06  | 1
1       |  2016-01-07  | 1
2       |  2016-01-08  | 2
2       |  2016-01-09  | 2

Ik heb gisteren window-functions ontdekt en dacht dat dit wellicht zou helpen bij de uitkomst. Maar ik heb nog wat problemen bij het praktisch toepassen van deze window-functions, hoewel ik er al wel een praktische toepassing voor had gevonden.

Een group is niet echt een optie, omdat per regel nog wat gegevens variëren waardoor een group niet echt een optie is (met een subquery zou het waarschijnlijk nog wel kunnen).

Ik wil eigenlijk voorkomen om subqueries en joins te doen, omdat de query tamelijk complex is (400 regels). Verder kan ik niet zo goed de juiste terminologie verzinnen, dus graag jullie hulp.

Ik gebruik Postgresql 9.4, mocht dat helpen. Overlap op datums kan nooit voorkomen, evenals een gat tussen de data.

[ Voor 6% gewijzigd door storeman op 03-03-2016 22:28 ]

[DiedX]

Ik mis alleen nog een vraag?

[P_de_B]

Tja, het juiste antwoord is toch een GROUP BY en een COUNT.

Dat je een subquery probeert te vermijden begrijp ik wel, maar waarom een join vermijden? Dat is waar databases goed in zijn. Als ik lees dat je query uit 400 regels bestaat denk ik dat je eerder andere problemen hebt dan een extra JOIN.

[storeman]

Tja. Eigen designfouten waar we voorlopig toch even me moeten werken. Dat wordt wel anders, maar daar heb ik nu even niets aan natuurlijk.

Hoe zie je die GROUP BY en COUNT voor je? Het punt is namelijk dat ik alleen de aansluitende wil tellen. Zodra men een dag in een andere plaats is, moet de teller dus weer op 0 gaan staan. Het totaal aantal nachten is natuurlijk niet zo moeilijk, de moeilijkheid is het zien wanneer de opeenvolging stopt en de teller weer op 0 kan. Daar heb ik dus geen ideeën over.

[P_de_B]

Mja, ik heb duidelijk niet goed genoeg gelezen :x

Ik snap het nu, maar heb nu even te weinig tijd om mee te denken. Zal nog even kijken of het straks lukt

[McBrag]

ORDER BY gebruiken? per stad de aansluitende dagen optellen?

[storeman]

McBrag schreef op donderdag 03 maart 2016 @ 20:26:
ORDER BY gebruiken? per stad de aansluitende dagen optellen?

Je bedoelt dit dan in de applicatielaag oplossen? Dat is eigenlijk iets wat ik wil voorkomen.

[sig69]

Klopt je voorbeeld voor id_city 3? Zo ja snap ik er niks van.

[storeman]

sig69 schreef op donderdag 03 maart 2016 @ 22:24:
Klopt je voorbeeld voor id_city 3? Zo ja snap ik er niks van.

Oeps, dat sloeg inderdaad nergens op. Ik heb het aangepast.

Ik heb het ondertussen opgelost in de applicatielaag, maar dat is niet echt wenselijk. Bovendien houd ik wel van een SQL uitdaging zo nu en dan. Dit probleem ga ik nog veel vaker tegen komen, dus een efficiente oplossing blijft welkom en leerzaam.

[ Voor 36% gewijzigd door storeman op 03-03-2016 22:30 ]

[sig69]

Ah duidelijk. In Sql Server zou ik iets met row number / rank en partition by doen denk ik (per aansluitende set een nummer genereren), daar een group by overheen met als uitkomt een count en een min en max datum, en daarna een join op de originele data voor het uiteindelijke resultaat.
Heb je in Postgress ook de beschikking over dat soort functies?

[storeman]

Zeker. Dat soort windowing functies bestaan, maar ken ik sinds gisteren, laat staan dat ik het praktisch kan toepassen.

row_number heb ik inderdaad overwogen, icm met partitioning. Het probleem blijft dat je moeilijk kan zien wanneer de reeks wordt onderbroken, dus de teller gaat resetten.

[sig69]

Ik wist dat ik goed zat number / rank functies, maar google was sneller dan zelf maken: http://stackoverflow.com/...-in-a-column-sequentially
Aangepast en getest met jouw data:

SQL:

select 
    id_city
    , date
    , [count] = count(*) over(partition by n ,c)
from 
(
    select 
        id_city
        , date, n 
        , c = ROW_NUMBER() over(order by n, date) - ROW_NUMBER() over(partition by id_city, n order by date)
    from 
    (
        select 
            id_city
            , date 
            , n = ROW_NUMBER() over(order by date) - ROW_NUMBER() over(partition by id_city order by date)
        from overnachting
    ) as x
) as v
order by date

Output:

code:

id_city date    count
1   2016-01-01  3
1   2016-01-02  3
1   2016-01-03  3
2   2016-01-04  2
2   2016-01-05  2
3   2016-01-06  1
1   2016-01-07  1
2   2016-01-08  2
2   2016-01-09  2

[storeman]

Wow, zeer nice!

Ik heb even een complete werkende snippet voor Postgresql

SQL:

CREATE TEMPORARY TABLE tmp_set (id_city INTEGER, visit_date DATE) ON COMMIT DROP;
INSERT INTO tmp_set (id_city, visit_date) VALUES (1, '2016-01-01'),  (1, '2016-01-02'),  (1, '2016-01-03'),  (2, '2016-01-04'),  (2, '2016-01-05'),  (3, '2016-01-06'),  (1, '2016-01-07'),  (2, '2016-01-08'),  (2, '2016-01-09');

select 
    id_city
    , visit_date
    , count(*) over(partition by n ,c) AS "count"
from 
(
    select 
        id_city
        , visit_date, n 
        , ROW_NUMBER() over(order by n, visit_date) - ROW_NUMBER() over(partition by id_city, n order by visit_date) AS c
    from 
    (
        select 
            id_city
            , visit_date 
            , ROW_NUMBER() over(order by visit_date) - ROW_NUMBER() over(partition by id_city order by visit_date) AS n
        from tmp_set
    ) as x
) as v
order by visit_date