Dotaz: Spotřeba vody za období ze stavu vodoměru

[pedro]

Zdravím,

asi nejsem dost zkušený, problém vypadá triviálně, ale lámu si hlavu s elegantním řešením.

Mějme tabulku datum date,stav integer. Například:

1.3.2014 1000
1.6.2014 1500
1.9.2014 2100

což je datum a stav vodoměru v daný den. Chci z toho dostat toto:

1.3.2014-1.6.2014 500
1.6.2014-1.9.2014 600

tedy kolik se spotřebovalo vody vždy mezi jednotlivými zápisy. Prosím o radu na elegantní řešení v PostgreSQL.

Díky.

---

---

Odpovědi

[Tarmaq]

SELECT s1.datum AS od, s2.datum AS do, s2.stav - s1.stav AS rozdil
FROM spotreba_vody s1
CROSS JOIN spotreba_vody s2
WHERE s1.datum < s2.datum

Na PostgreSQL netestovano, ale melo by to fungovat. Ma to jednu odlisnost, vraci to i rozdil mezi 1.3. a 1.9.

[Tarmaq]

SELECT x.od, x.do, s2.stav - s1.stav AS rozdil
FROM
(
  SELECT 
    x1.datum AS od
  , (SELECT MIN(x2.datum) FROM spotreba_vody x2 WHERE x2.datum > x1.datum) AS do
  FROM spotreba_vody x1
) x
JOIN spotreba_vody s1 ON s1.datum = x.od
JOIN spotreba_vody s2 ON s2.datum = x.do

Tohle vrati jen ty dva zaznamy, ale opet nevim, jestli funguje i na postgresu..

[Tomáš]

Máte dvě možnosti. První méně efektivní se self joinem a druhý lepší s window funkcemi.

1. select 
     A.dt
     ,min(B.dt)
     ,min(B.val order by B.dt)-A.val 
   from T as A 
   join T as B on A.dt<B.dt 
   group by A.*
   

2. select 
     A.dt
     ,max(A.dt) over( order by A.dt rows between current row and 1 following)
     ,max(A.val) over( order by A.dt rows between current row and 1 following)-A.val
   from A
   

Psáno bez jakékoliv kontroly, takže tam budou asi nějaké chybky. Ale jako nápověda je to dostatečné. BTW: Co když není datum unikátní?

[rADOn]

Ta druha varianta se mi libi… umi neco takovyho mysql?

[Tomáš]

Pokud vím, tak ne. Musel by jste si pomoci cursorem.

[pedro]

Z hlavy (skoro) perfektní, jen na konci zbude řádek, kde je stejné počáteční i koncové datum a nulová spotřeba. Výsledný select tedy vypadá takto:

select * from
(
select
  voda.datum as zacatek
  ,max(voda.datum) over( order by voda.datum rows between current row and 1 following) as konec
  ,max(voda.stav) over( order by voda.datum rows between current row and 1 following)-voda.stav as spotreba
from myschema.voda
) foo
where zacatek != konec;

Nejedinečné datum s různými stavy vodoměru je blbost, tak přidán constraint (ve skutečnosti je to timestamp, ne datum).

K dokonalosti ještě chybí ošetřit přetočení či výměnu vodoměru, ale to teď řešit nebudu.

[Tarmaq]

Co např.:

postgres=> select * 
              from (select mereno, hodnota - lag(hodnota) over (order by mereno) as spotreba 
                       from odecet order by 1) s 
             where spotreba is not null;
   mereno   | spotreba 
------------+----------
 2014-01-06 |      500
 2014-01-09 |      600
(2 rows)

Kartézákům se pokud možno vyhýbejte - pro větší data to může být docela pomalé, a navíc zápis s window funckcemi je výrazně čitelnější.

[pedro]

Moc všem děkuji. Ano, šlo mi hlavně o window funkce, které jsem pro sebe objevil teprve včera ;).

Kartézákům jsem se chtěl vyhnout, ačkoli v mém případě by to asi nevadilo, protože se jedná skutečně o vodoměr v bytovém domě a asi ho nebudu odečítat častěji než 1x týdně, takže i za léta řádově stovky řádků.

Tak teď ještě ty window funkce pochopit a naučit se je.

S vodou není legrace, jeden protékající záchod v domě dokáže udělat na faktuře za vodu opravdu pořádný vítr.