获取上周唯一的最新数据并对某些列求和

仅获取上周的最新数据并对某些列求和

我用数据、实际结果和预期做了一个例子。

http://rextester.com/HMB12638 http://rextester.com/HMB12638

 --Taking first as example..

--      user    contact         barcode date                in  out dif
-- 1    USER2   Guillermo Tole  987654  16.06.2017 05:27:00 500 420 80
-- 2    USER2   Guillermo Tole  281460  15.06.2017 05:36:00 310 220 90
-- 3    USER2   Guillermo Tole  987654  13.06.2017 05:27:00 400 380 20
-- 4    USER2   Guillermo Tole  281460  12.06.2017 05:26:00 230 190 40
-- 5    USER3   Juan Rulfo      123456  15.06.2017 05:37:00 450 300 150
-- 6    USER3   Juan Rulfo      123456  12.06.2017 05:37:00 450 300 150
-- 7    USER3   Pepito Marquez  346234  15.06.2017 05:37:00 600 360 240
-- 8    USER3   Pepito Marquez  346234  14.06.2017 05:37:00 450 300 150


 -- This would be the expectation
-- (MOST RECENT in . out) SUM of all the barcodes showed
--      user    contact         barcode date                in  out sum
-- 1    USER2   Guillermo Tole  987654  16.06.2017 05:27:00 500 420 170 (80 + 90)
-- 2    USER2   Guillermo Tole  281460  15.06.2017 05:36:00 310 220 170 (80 + 90)
-- 5    USER3   Juan Rulfo      123456  15.06.2017 05:37:00 450 300 150
-- 7    USER3   Pepito Marquez  346234  15.06.2017 05:37:00 600 360 240

我认为这符合您的预期结果：

select "user", "contact", "barcode", "date", "in", "out","dif"
     , sum("in"-"out") over(partition by "user", "contact") as "sum"
from (
    select "user", "contact", "barcode", "date", "in", "out","dif"
    , lag(dif,1) over(partition by "user", "contact" order by "date" ASC) prevdif
    , row_number() over(partition by "user", "contact" order by "date" DESC) rn
    from "table1" 
    where date_trunc('day', "date") <= '2017-06-25' ::date - (  interval '1 week')::interval 
    and "date" >  '2017-06-25'::date - (  interval '2 weeks')::interval 
    ) d
where rn in (1,2) and prevdif is not null
order by 1,2,4 DESC

Result:

+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+
|    | user  |    contact     | barcode |        date         | in  | out | dif | sum |
+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+
|  1 | USER2 | Guillermo Tole |  987654 | 16.06.2017 05:27:00 | 500 | 420 |  80 | 170 |
|  2 | USER2 | Guillermo Tole |  281460 | 15.06.2017 05:36:00 | 310 | 220 |  90 | 170 |
|  3 | USER3 | Juan Rulfo     |  123456 | 15.06.2017 05:37:00 | 450 | 300 | 150 | 150 |
|  4 | USER3 | Pepito Marquez |  346234 | 15.06.2017 05:37:00 | 600 | 360 | 240 | 240 |
+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+

See: http://rextester.com/ISHS42170 http://rextester.com/ISHS42170

对于诸如“最近”之类的条件，我发现使用 ROW_NUMBER() OVER() 是最方便的，因为它允许返回每个“最近”事件的整行，如果使用 MAX() 和通过...分组。通过过滤函数返回的值为 1 的行来返回“不同”结果。

+EDIT

而不是使用where rn in (1,2)我相信更好的方法是在 OVER(PARTITION BY...) 条件下使用条形码，如下所示：

select "user", "contact", "barcode", "date", "in", "out","dif"
     , sum("in"-"out") over(partition by "user", "contact") as "sum"
from (
    select "user", "contact", "barcode", "date", "in", "out","dif"
    , lag(dif,1) over(partition by "user", "contact", "barcode" order by "date" ASC) prevdif
    , row_number() over(partition by "user", "contact", "barcode" order by "date" DESC) rn
    from "table1" 
    where date_trunc('day', "date") <= '2017-06-25' ::date - (  interval '1 week')::interval 
    and "date" >  '2017-06-25'::date - (  interval '2 weeks')::interval 
    ) d
where rn = 1 and prevdif is not null
order by 1,2,4 DESC

http://rextester.com/SCV98254 http://rextester.com/SCV98254