我有下表:
<id: 1, location: "New York" product_id: 1, amount: 10, date: "2020-06-23T20:04:12">
<id: 2, location: "New York" product_id: 1, amount: 20, date: "2020-06-20T00:00:45">
<id: 3, location: "New York" product_id: 2, amount: 50, date: "2020-06-19T23:26:21">
<id: 4, location: "New York" product_id: 3, amount: 50, date: "2020-06-24T00:00:00">
<id: 5, location: "Ottawa" product_id: 1, amount: 20, date: "2020-06-12T00:00:00">
<id: 6, location: "Ottawa" product_id: 1, amount: 30, date: "2020-06-22T00:00:00">
<id: 7, location: "Ottawa" product_id: 2, amount: 40, date: "2020-06-23T00:00:00">
我想编写一个原始的sql查询,将amount
每个位置的总数相加,但是每个产品id只添加一次。因此,当有相似product_id
的记录时,它选择的记录是最接近但小于max_date
变量的记录。例如,假设max_date
=“ 2020-06-24T00:00:00”,查询上表时的响应为:
{
"New York":60, #exludes ids 2, 3
"Ottawa": 70 #exludes id 4
}
任何帮助都会很棒。抱歉,如果这是一个菜鸟问题,请使用postgresql(或sql)进行更新。
请尝试这个。
该keeps
CTE截止之前限制查询记录"date"
及受让人号码与您行。
然后,主查询仅选择row_number()
was中的那些1
,并按位置计算总和。
with keeps as (
select location, product_id, amount,
row_number() over (partition by location, product_id
order by "date" desc) as rn
from sales
where "date" < '2020-06-24'
)
select location, sum(amount) as amount
from keeps
where rn = 1
group by location;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句