sql - 当前和上一年的每周运行总计

Question

此查询提供当年和上一年至今的价格和平方英尺的年初至今数字。这更像是当年和上一年的运行总计，在这种情况下，从 1 到 7 等周......(2017 年第 7 周结束于 02/19/2017)当年和上一年(2016 年第 7 周结束于 02/22/2016)。我之所以使用子查询，是因为这是我所知道的唯一解决这种情况的方法。当然，如果您认为执行此查询有更短、可行的替代方案，请提出建议。

Actual_Sale_Date 包含一周中所有 7 天的数据，但我们在星期日截止，这就是为什么 2/22/2016(星期日结束 2016 年第 7 周)和 2/19/2017 (2017 年第 7 周的周日结束)。

我试过 "Actual_Sale_Date"= date_trunc('week', now())::date - 1 这个函数只返回过去星期日结束的前一周数据。我查看了 interval，因为 dateadd 在 postgresql 中不存在，但我无法解决它。

我的查询:

select (money(Sum("Price") / COUNT("Price"))) as "Avg_Value YTD",
Round(Avg("Price"/"Sq_Ft"),+2) as "Avg_PPSF YTD",

(select 
(money(Sum("Price") / COUNT("Price")))  from allsalesdata
where "Actual_Sale_Date" >=  '01/01/2016' AND "Actual_Sale_Date" < '02/22/2016'
and "Work_ID" = 'SO') AS "Last Year at this time Avg_Value", 


(select Round(Avg("Price"/"Sq_Ft"),+2)
from allsalesdata
where "Actual_Sale_Date" >=  '01/01/2016' AND "Actual_Sale_Date" <  '02/22/2016' 
and "Work_ID" = 'SO') AS "Last Year at this time Avg_PPSF"


from allsalesdata
where "Actual_Sale_Date" >=  '01/01/2017' AND "Actual_Sale_Date" <'02/20/2017'
and "Work_ID" = 'SO'

示例数据:

Price       Sq_Ft        Actual_Sale_Date        Work_ID

45871       3583           01/15/2016             SO
55874       4457           02/05/2016             SO
88745       4788           02/20/2016             SO
58745       1459           01/10/2016             SO
88749       2145           01/25/2017             SO
74856       1478           01/25/2017             SO
74586       4587           01/31/2017             ABC
74745       1142           02/10/2017             SO
74589       2214           02/19/2017             SO

Answer 1

这应该是你需要的(假设你有最新版本的 PG):

SELECT DISTINCT wk AS "Week",
       sum("Price")::money FILTER (WHERE yr = 2017) OVER w / 
       count("Price") FILTER (WHERE yr = 2017) OVER w AS "Avg_Value YTD",
       sum("Price")::money FILTER (WHERE yr = 2017) OVER w / 
       sum("Sq_Ft") FILTER (WHERE yr = 2017) OVER w AS "Avg_PPSF YTD",
       sum("Price")::money FILTER (WHERE yr = 2016) OVER w / 
       count("Price") FILTER (WHERE yr = 2016) OVER w AS "Last Year this time Avg_Value",
       sum("Price")::money FILTER (WHERE yr = 2016) OVER w / 
       sum("Sq_Ft") FILTER (WHERE yr = 2016) OVER w AS "Last Year this time Avg_PPSF",
FROM (
    SELECT extract(isoyear from "Actual_Sale_Date")::integer AS yr,
           extract(week from "Actual_Sale_Date")::integer AS wk,
           "Price", "Sq_Ft"
    FROM allsalesdata
    WHERE "Work_ID" = 'SO') sub
-- optional, show only completed weeks in this year:
WHERE wk <= extract(week from CURRENT_DATE)::integer - 1 
WINDOW w AS (ORDER BY wk)
ORDER BY wk;

在内部查询中，为每次销售提取销售日期的年份和星期。根据您的要求，一周从星期一开始。

在主查询中，这些行作为单个分区 frame 处理，即从分区的开始(= 第一行)到当前的 最后一个 peer排。由于窗口定义按 wk 对行进行排序，因此从开始(周 = 1)到当前周的所有行都包含在汇总中。这将为您提供运行总计。 sum() 和 count() 函数按相关年份进行过滤，DISTINCT 子句确保您每周只获得一行.