sql - 每周和当前年度的总计

此查询提供当前年度和上一年度迄今的价格和平方英尺的年度迄今数字。这更像是当前年度和上一年度相对于本例中从1到7周的运行总数，依此类推。。。。。本年度和上一年度（2017年第7周于2017年2月19日结束）（2016年第7周于2016年2月22日结束）。我之所以使用子查询，是因为这是我知道的解决这种情况的唯一方法。当然，如果您认为执行此查询有一个更短、可行的替代方法，请给出建议。
Actual_Sale_Date保存一周中所有七天的数据，但我们在星期日切断，这就是为什么2016年2月22日（星期日结束于2016年第7周）和2017年2月19日（星期日结束于2017年第7周）的原因。
我尝试了"Actual_Sale_Date" = date_trunc('week', now())::date - 1这个函数只返回前一周的数据，结束于过去的星期天。我看了interval，因为不存在于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

最佳答案

这应该是您需要的（假设您有最新版本的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;

在内部查询中，为每次销售提取销售日期的年份和周。根据你的要求，本周从星期一开始。
在主查询中，这些行被作为单个分区帧处理，即从分区的开始（=第一行）到当前行的最后一个对等点。由于窗口定义按wk对行进行排序，因此从开始（week=1）到当前周的所有行都包含在摘要中。这将为您提供运行总数。sum()和count()函数按所讨论的年份进行筛选，DISTINCT子句确保您每周只能获得一行。

关于sql - 每周和当前年度的总计，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42427428/