如何将当前月份的值与过去 12 个月进行比较

Question

我正在尝试将当前月份的值与过去 12 个月的值进行比较。

E.g:
6/30/2019 100
5/31/2019 90
4/30/2019 80
3/31/2019 70
2/8/2019 60
1/31/2019 50
12/31/2018 40
11/30/2018 30
10/31/2018 20
9/30/2018 10
8/31/2018 90
7/30/2018 110

现在当前月份值(6/30/2019(在100中，那么我想将这些值与过去12个月的值进行比较。如果当前月份值是与 12 个月相比的最大值，那么我想将标志设置为"max"。在上面的例子中，110 是最大值，但当前月份为 100，即与过去 12 个月的值相比，它是最小的值，然后我想将标志设置为"min"。

另外，我还想获取日期，即，如果它是最小的，那么日期的最大值是多少(预期输出"MIN(110 截至 7 年 30 月 2018 日("(。

请为我提供任何解决方案来实现此方案

预期输出"MIN(110 截至 2018 年 7 月 30 日(">

Answer 1

如果要将当前值与滚动的 12 个月最小值/最大值进行比较，可以使用窗口函数：

select top (1) t.*,
       (case when value = max12_value then 'MAX'
             when value = min12_value then 'MIN'
        end) as flag
from (select t.*,
             max(value) over (order by date rows between 11 preceding and current row) as max12_value,
             min(value) over (order by date rows between 11 preceding and current row) as min12_value,
      from t
     ) t
order by date desc

Answer 2

如果我理解正确，这里有一种方法可以做到这一点：

-- Create a table variable to hold the sample data
DECLARE @data TABLE (
    [Date] DATE,
    [Value] INT
)
-- Load the sample data table
DECLARE @i INT = 0;
WHILE @i < 12
BEGIN
    INSERT INTO @data ([Date], [Value]) values (EOMONTH(DATEADD(M,-@i, GETDATE())), FLOOR(RAND()*(100-1+1))+10);
    SET @i = @i + 1;
END;
-- Select the resulting data set with flags
WITH t as
(
SELECT [Date], [Value], 
    CASE 
        WHEN [Value] = (SELECT MAX([Value]) FROM @data) THEN 'MAX' 
        WHEN [Value] = (SELECT MIN([Value]) FROM @data) THEN 'MIN' 
        ELSE '' 
    END AS Flag 
FROM @data
)
SELECT 'MAX(' + CAST([Value] as VARCHAR(MAX)) + ' as on ' + CAST([Date] as VARCHAR(MAX)) + ')' FROM t WHERE [Flag] = 'MAX'

这将输出：

MAX(109 as on 2018-09-30)

将"MIN"换成"MAX"，您将获得最小值。

Answer 3

以下脚本将为您提供输出，考虑到始终从 GETDATE(( 计算的当前月份值

WITH CTE (d, value)
AS
(
    SELECT id,value FROM your_table
),
CTE2
AS
(
    SELECT DISTINCT
    (SELECT Value FROM CTE WHERE d>= CAST(DATEADD(DD,-DATEPART(DD,GETDATE()) + 1,GETDATE())  AS DATE)) current_month_value,
    MIN(value) min_value,
    MAX(value) max_value
    FROM CTE
    WHERE d <  CAST(DATEADD(DD,-DATEPART(DD,GETDATE()) + 1,GETDATE())  AS DATE)
    AND d >= CAST(DATEADD(MM,-13,DATEADD(DD,-DATEPART(DD,GETDATE()) + 1,GETDATE()) ) AS DATE)
)
SELECT 
CASE 
    WHEN  current_month_value > max_value THEN 'MAX'
    ELSE 'MIN(' + CAST(max_value AS VARCHAR)+ ' AS ON '+ (SELECT TOP 1 CAST(d AS VARCHAR) FROM CTE WHERE Value = max_value)+ ')'
END
FROM CTE2