sql - PostgreSQL:将 SQL 查询简化为更短的查询

Question

我有一个名为“daily_prices”的表，其中列有“sale_date”、“last_sale_price”、“symbol”。

我需要计算“last_sale_price”在 10 周内与前一天的“last_sale_price”相比上涨了多少倍。

目前我有 2 周这样的查询:

select count(*) as "timesUp", sum(last_sale_price-prev_price) as "dollarsUp", 'wk1' as "week"
from 
(
    select last_sale_price, LAG(last_sale_price, 1) OVER (ORDER BY sale_date) as prev_price 
 from daily_prices 
where sale_date <= CAST('2020-09-18' AS DATE) AND sale_date >= CAST('2020-09-14' AS DATE)
and symbol='AAPL'
) nest
where last_sale_price > prev_price

UNION

select count(*) as "timesUp", sum(last_sale_price-prev_price) as "dollarsUp", 'wk2' as "week"
from 
(
    select last_sale_price, LAG(last_sale_price, 1) OVER (ORDER BY sale_date) as prev_price 
 from daily_prices 
where sale_date <= CAST('2020-09-11' AS DATE) AND sale_date >= CAST('2020-09-07' AS DATE)
and symbol='AAPL'
) nest
where last_sale_price > prev_price

我正在使用“UNION”合并每周数据。但随着周数的增加，查询将变得庞大。有没有更简单的方法来编写此查询？任何帮助深表感谢。提前致谢。

Answer 1

您可以从 sale_date 中提取星期。然后在上层查询上应用 group by

select EXTRACT(year from sale_date) YEAR, EXTRACT('week' FROM sale_date) week, count(*) as "timesUp", sum(last_sale_price-prev_price) as "dollarsUp"
from (
     select  
        sale_date, 
        last_sale_price, 
        LAG(last_sale_price, 1) OVER (ORDER BY sale_date) as prev_price 
     from daily_prices 
     where symbol='AAPL'
  )
where last_sale_price > prev_price
group by EXTRACT(year from sale_date), EXTRACT('week' FROM sale_date) 

要仅提取工作日，您可以添加此过滤器

EXTRACT(dow FROM sale_date) in (1,2,3,4,5)

PS:确保星期一是一周的第一天。在某些国家/地区，星期日是一周的第一天