postgre过滤掉 SQL 查询中窗口函数 lag() 产生的空值

Question

示例查询：

SELECT *, 
lag(sum(sales), 1) OVER(PARTITION BY department
ORDER BY date ASC) AS end_date_sales
FROM revenue
GROUP BY department, date;

我只想显示end_date未NULL的行。

是否有专门用于这些情况的条款？WHERE或HAVING不允许聚合或窗口函数情况。

Answer 1

一种方法使用子查询：

SELECT r.*
FROM (SELECT r. *, 
LAG(sum(sales), 1) OVER (ORDER BY date ASC) AS end_date
FROM revenue r
) r
WHERE end_date IS NOT NULL;

也就是说，我认为查询不正确，因为您编写了它。 我假设你想要这样的东西：

SELECT r.*
FROM (SELECT r. *, 
LEAD(end_date, 1) OVER (PARTITION BY ? ORDER BY date ASC) AS end_date
FROM revenue r
) r
WHERE end_date IS NOT NULL;

其中?是客户 ID 等列。

Answer 2

试试这个

select * from (select distinct *,SUM(sales) OVER (PARTITION BY dept) from test)t 
where t.date in(select max(date) from test group by dept)
order by date,dept;

还有一种更简单的方法，没有子查询

SELECT distinct dept,MAX(date) OVER (PARTITION BY dept),
SUM(sales) OVER (PARTITION BY dept) 
FROM test;