获取上周的唯一最新数据并汇总一些列 [英] Get the only latest data from last week and sum some column
问题描述
仅获取上周的最新数据并汇总一些列
Get only latest data from last week and sum some column
我以dat为例，说明了实际结果和预期结果。
I made an example with dat, the actual result and the expected.
--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
推荐答案
我认为这符合您的预期结果：
I think this matches your expected result:
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
结果：
+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+
| | 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 |
+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+
请参阅： http：// rextester。 com / ISHS42170
对于诸如最新这样的条件，我发现使用ROW_NUMBER（）OVER（）最方便，因为它允许整行返回每个最新事件的结果，如果使用MAX（）和GROUP BY，则不是那么简单。通过过滤函数返回的值为1的行来返回不同结果。
For conditions such as "most recent" I find that using ROW_NUMBER() OVER() is the most convenient as it allows the whole row of each "most recent" event be returned which isn't quite so simple if using MAX() and GROUP BY. "Distinct" results are returned by filtering the rows having value 1 returned by the function.
+ EDIT
我相信最好的方法是使用（$ 1,2） 而不是最好在OVER（PARTITION BY ...）条件下使用条形码，例如：
Instead of using where rn in (1,2) I believe a better way would have been better to use barcode in the OVER(PARTITION BY...) conditions, like this:
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
这篇关于获取上周的唯一最新数据并汇总一些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
