参数月份选择：查询显示前一个月，12个月前和过去12个月的平均值

问题描述：

我想玩我的Total_Sales表。这是数据看起来的样子（使用SQL Server 2008 R2）参数月份选择：查询显示前一个月，12个月前和过去12个月的平均值

Name Year Month Sales 
------ ---- ----- ----- 
Alfred 2011 1  100 
Alfred 2011 2  200 
Alfred 2011 3  300 
Alfred 2011 4  400 
Alfred 2011 5  500 
Alfred 2011 6  600 
Alfred 2011 7  700 
Alfred 2011 8  800 
Alfred 2011 9  900 
Alfred 2011 10  500 
Alfred 2011 11  500 
Alfred 2011 12  500

的SQL查询我想创建应显示这样的数据：

Name Year Month Sales Prev_Month Month_Last_Year_Sales Last_12_Month_AVG 
------ ---- ----- ----- ---------- --------------------- ----------------- 
Alfred 2011 1  100 NULL  (year 2010, month 1) (2010_01 to 2011_01)/(12) 
Alfred 2011 2  200 100  (year 2010, month 2) (2010_02 to 2011_02)/(12) 
Alfred 2011 3  300 200  (year 2010, month 3) (2010_03 to 2011_03)/(12) 
Alfred 2011 4  400 300  (year 2010, month 4) (2010_04 to 2011_04)/(12) 
Alfred 2011 5  500 400  (year 2010, month 5) (2010_05 to 2011_05)/(12) 
Alfred 2011 6  600 500  (year 2010, month 6) (2010_06 to 2011_06)/(12) 
Alfred 2011 7  700 600  (year 2010, month 7) (2010_07 to 2011_07)/(12) 
Alfred 2011 8  800 700  (year 2010, month 8) (2010_08 to 2011_08)/(12) 
Alfred 2011 9  900 800  (year 2010, month 9) (2010_09 to 2011_09)/(12) 
Alfred 2011 10  500 900  (year 2010, month 10) (2010_10 to 2011_10)/(12) 
Alfred 2011 11  500 500  (year 2010, month 11) (2010_11 to 2011_11)/(12) 
Alfred 2011 12  500 500  (year 2010, month 12) (2010_12 to 2011_12)/(12)

要复制的前一个月，我使用此：Copy prior month value and insert into new row

SELECT 
TS.name, 
TS.year, 
TS.month, 
TS.sales, 
COALESCE(TS2.sales, 0) AS prior_month_sales 
FROM 
TotalSales TS 
LEFT OUTER JOIN TotalSales TS2 ON 
TS2.name = TS.name AND 
(
    (TS2.year = TS.year AND TS2.month = TS.month - 1) OR 
    (TS.month = 1 AND TS2.month = 12 AND TS2.year = TS.year - 1) 
)

在Prev_Month的NULL是表明TOTAL_SALES开始在2011年第1个月，所以这个例子之前没有数据。

我打算使用一个参数，在其中选择一个月。
感谢您的帮助！

你可以在报告服务，而不是SQL的聚合。获取两年数据和使用报告功能 – niktrs

这就是我的想法，但是为一个数据集创建查询不会更容易吗？我遇到的问题是Last_12_Month_AVG。 – AceAlfred

我设法完全通过报告服务完成 – niktrs

答

SELECT 
    [this_month].*, 
    [last_month].Sales  AS [prev_month_sales], 
    [last_year].Sales   AS [month_last_year_sales], 
    [yearly].AverageSales  AS [last_12_month_average] 
FROM 
    Total_Sales  AS [this_month] 
LEFT JOIN 
    Total_Sales  AS [last_month] 
    ON [last_month].Name = [this_month].Name 
    AND (
     ([last_month].Year = [this_month].Year  AND [last_month].Month = [this_month].Month - 1) 
     OR ([last_month].Year = [this_month].Year - 1 AND [last_month].Month = 12 AND [this_month].Month = 1) 
    ) 
LEFT JOIN 
    TotalSales  AS [last_year] 
    ON [last_year].Name = [this_month].Name 
    AND [last_year].Year = [this_month].Year - 1 
    AND [last_year].Month = [this_month].Month 
CROSS APPLY 
(
    SELECT 
    AVG(Sales) AS AverageSales 
    FROM 
    Total_Sales 
    WHERE 
    Name = [this_month].Name 
    AND (
      (Year = [this_month].Year  AND Month <= [this_month].Month) 
     OR (Year = [this_month].Year - 1 AND Month > [this_month].Month) 
    ) 
) 
    AS [yearly]

平均值不是12的值，因为前一年的数据并不总是12个月。但AVG（）函数会为您提供帮助。

此外，我会高度推荐使用YEAR和MONTH字段。相反，我会建议使用日期时间字段来表示“月开始”，并使用SQL Server的日期函数...

Last Month : MonthStart = DATEADD(MONTH, -1, ThisMonth) 
A Year Ago : MonthStart = DATEADD(YEAR, -1, ThisMonth) 
Last Year : MonthStart > DATEADD(YEAR, -1, ThisMonth) AND MonthStart <= ThisMonth

Nice Dems，我正在调整它到我的数据来看它是如何工作的。 – AceAlfred

感谢您的输入！ – AceAlfred

这是一种魅力！ – AceAlfred

答

另一个答案，我不知道是快或不...

WITH sales AS (
    SELECT 
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Year, Month) AS month_id, 
    * 
    FROM 
    yearly_sales 
) 
SELECT 
    Name  = [this_month].Name, 
    Year  = MAX([this_month].Year), 
    Month  = MAX([this_month].Month), 
    Sales  = MAX([this_month].Sales), 
    Last_Month = MAX(CASE WHEN [13_months].month_id = [this_month].month_id - 1 THEN [13_months].Sales END), 
    Last_Year = MAX(CASE WHEN [13_months].month_id = [this_month].month_id - 12 THEN [13_months].Sales END), 
    Yearly_AVG = AVG(CASE WHEN [13_months].month_id > [this_month].month_id - 12 THEN [13_months].Sales END) 
FROM 
    Sales  AS [this_month] 
INNER JOIN 
    Sales  AS [13_months] 
    ON [13_months].Name  = [this_month].Name 
    AND [13_months].month_id <= [this_month].month_id 
    AND [13_months].month_id >= [this_month].month_id - 12 
GROUP BY 
    [this_month].Name

Last_12_Months_AVG问题。这是否仅显示有12个月可用时间的值，并显示月份不足时显示为空/零？ – AceAlfred

如所形成的，每个销售值都提供给AVG函数，或者替换为NULL，然后提供给AVG函数。（CASE没有ELSE，但默认为在隐式ELSE语句中返回NULL。）AVG（）函数忽略所有NULL（因此平均值为2，NULL仍为2）。如果提供给AVG（）的所有值都为NULL，则结果为NULL - 但目前代码包含“本月”作为平均值的一部分，因此它始终具有至少一个实际值。 – MatBailie

我遇到了一个问题，也许你知道一个快速修复？当员工尚未预订前一个月的销售时，没有要为此人显示的数据。有没有办法添加一行与失踪员工，其中“销售”设置为0，仍然拉动其他行的数据？例如， 2012年 - 第一个月 - 名称Alfred - 销售0 - 上一个500等 – AceAlfred

答

从AceAlfred -
One problem I have run into, maybe you know a quick fix? When a employee has not booked his sales for a previous month there is no data to display for this individual. Is there a way to add a row with the missing employee, where the "sales" is set to 0 and still pull the data for the other rows? Ex. Year 2012 -- Month 1 -- Name Alfred -- Sales 0 -- Prev 500

一种方法是“修理”你的数据，以确保它总是有其价值。我建议在任何系统中填充数据。或者作为一个夜间批次来检查没有输入数据的人，并为你提供0（如果/当真实数据到达时更新）。但如果你不能...

CREATE TABLE agent (id INT, name NVARCHAR(128), start_date DATETIME, leave_date DATETIME); 
-- populate with your agents 

CREATE TABLE calendar (year DATETIME, month DATETIME, day DATETIME); 
-- populate with all dates you want to report on 

CREATE TABLE sales (agent_id INT, month_start DATETIME, total INT); 
-- populate with your data 


WITH new_raw_data AS 
(
    SELECT 
    agent.id     AS [agent_id], 
    calendar.month   AS [month_start], 
    COALESCE(sales.total, 0) AS [total] 
    FROM 
    agent 
    INNER JOIN 
    calendar 
     ON calendar.month_start >= COALESCE(DATEADD(month, -1, agent.start_date), '2000 Jan 01') 
     AND calendar.month_start <= COALESCE(agent.leave_date, '2079 Dec 31') 
    LEFT JOIN 
    sales 
     ON sales.agent_id = agent.id 
     AND sales.month_start = calendar.month_start 
    WHERE 
    calendar.month_start = calendar.day -- Only use records for the start of each month 
) 
, 
<your other queries, using the nicely cleaned data, go here.>

参数月份选择：查询显示前一个月，12个月前和过去12个月的平均值

相关推荐