用数据透视法计算最后两天的%数据变化

Question

我有一个表，其中包含每天收集的一组数据(我使用Server 2016)

Date        A     B     C    D
2017/5/22   1.1   2.1  3.1   4.1
2017/5/21   1.0   2.0  3.0   4.0
2017/5/20   0.9   1.9  2.9   3.9
2017/5/19   1.0   2.0  3.0   4.0
2017/5/18   1.1   2.1  3.1   4.1

我正在尝试编写一个查询，该查询将显示最后两天和%的变化，返回结果如下：

Field  Today   Yesterday  Change(%)
A       1.1       1.0       10.0%
B       2.1       2.0        5.0%
C       3.1       3.0        3.3%
D       4.1       4.0        2.5%

有什么快速的方法可以做到这一点(我假设支点在某个地方，但我真的无法完成这个查询)

Answer 1

我首先使用一个窗口函数来获得结果，比如row_number来获取前两个日期，然后将A、B、C和D的列分解为行。一旦你这样做了，你可以枢轴这些结果，以获得最终想要的产品。

把它分解，我会从使用row_number开始

select [Date], A, B, C, D,
  rn = row_number() over(order by [Date] desc) 
from #yourtable

这将为表中的每一行创建一个唯一的行id，您可以按日期对此进行排序，以按照您想要的顺序生成日期。接下来，您将将A、B、C和D列分解为行：

select 
  Field,
  value, 
  Dt = case when rn = 1 then 'Today' else 'Yesterday' end
from
(
  select [Date], A, B, C, D,
    rn = row_number() over(order by [Date] desc) 
  from #yourtable
) x
cross apply
(
  values
  ('A', A),
  ('B', B),
  ('C', C),
  ('D', D)  -- include additional columns here if you have more
) c (Field, value)
where rn <= 2 -- return top 2 dates

在这种情况下，您将把列转换为行，然后只返回您想要的前两个日期--今天和昨天。最后，您将将这些Today和Yesterday值转换为列，并计算您的百分比变化。所以把这些都放在一起：

select 
  Field,
  Today, 
  Yesterday,
  ChangePercent = round((Today-Yesterday)/ Yesterday *100.0, 2)
from
(
  select 
    Field,
    value, 
    Dt = case when rn = 1 then 'Today' else 'Yesterday' end
  from
  (
    select [Date], A, B, C, D,
      rn = row_number() over(order by [Date] desc) 
    from #yourtable
  ) x
  cross apply
  (
    values
    ('A', A),
    ('B', B),
    ('C', C),
    ('D', D)
  ) c (Field, value)
  where rn <= 2 -- return top 2 dates
) d
pivot
(
  max(value)
  for dt in (Today, Yesterday)
) piv

这是一个演示。这给出了结果：

Field Today Yesterday ChangePercent 
----- ----- --------- ------------- 
A     1.1   1         10            
B     2.1   2         5             
C     3.1   3         3.33          
D     4.1   4         2.5           

Answer 2

这应该能满足你的需要：

select
    FieldValue                          as  [Field]
    , case FieldValue 
        when 'A'
            then ta
        when 'B'
            then tb
        when 'C'
            then tc
        when 'D'
            then td
        end                             as  [Today]
    , case FieldValue 
        when 'A'
            then ya
        when 'B'
            then yb
        when 'C'
            then yc
        when 'D'
            then yd
        end                             as  [Yesterday]
    , Change                            as  [Change(%)]
    from
    (select
        t1.a                            as  [ta]    -- today's A value
        , t1.b                          as  [tb]    -- today's B value
        , t1.c                          as  [tc]    -- today's C value
        , t1.d                          as  [td]    -- today's D value
        --, t1.e                        as  [te]    -- today's E value
        -- make sure to include the t1.e, t1.f etc. for other Fields too

        , y.a                           as  [ya]    -- yesterday's A value
        , y.b                           as  [yb]    -- yesterday's B value
        , y.c                           as  [yc]    -- yesterday's C value
        , y.d                           as  [yd]    -- yesterday's D value
        --, y.e                         as  [ye]    -- yesterday's E value
        -- make sure to include the y.e, y.f etc. for other Fields too

        , 100 / (y.a / (t1.a - y.a))    as  [A]     -- A's change since yesterday
        , 100 / (y.b / (t1.b - y.b))    as  [B]     -- B's change since yesterday
        , 100 / (y.c / (t1.c - y.c))    as  [C]     -- C's change since yesterday
        , 100 / (y.d / (t1.d - y.d))    as  [D]     -- D's change since yesterday
        --, 100 / (y.e / t1.e - y.e))   as  [E]     -- E's change since yesterday (INCLUDE this "E" alias in the list of columns from UNPIVOT)
        -- make sure to add calculations for your other fields here too

    from baseTable t1
        cross apply (select top 1 * 
                     from baseTable t2 
                     where t2.date < t1.date) y
    where t1.date = (select max(date) from baseTable)
    ) result
unpivot (
    Change for FieldValue in (a, b, c, d) --, e, f etc.) -- enumerate all column ALIASES used in the sub-select, where the CHANGE is calculated
    ) as unpvt

只需确保使用所有值扩展CASE语句和UNPIVOT列，以及使用所有其他字段扩展100 / (x.a / (t1.a - x.a))。

用于生成示例数据的脚本：(理想情况下您应该提供以下内容)

CREATE TABLE [dbo].[baseTable](
    [date] [date] NULL,
    [a] [numeric](18, 1) NULL,
    [b] [numeric](18, 1) NULL,
    [c] [numeric](18, 1) NULL,
    [d] [numeric](18, 1) NULL
)

GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-22' AS Date), CAST(1.1 AS Numeric(18, 1)), CAST(2.1 AS Numeric(18, 1)), CAST(3.1 AS Numeric(18, 1)), CAST(4.1 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-21' AS Date), CAST(1.0 AS Numeric(18, 1)), CAST(2.0 AS Numeric(18, 1)), CAST(3.0 AS Numeric(18, 1)), CAST(4.0 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-20' AS Date), CAST(0.9 AS Numeric(18, 1)), CAST(1.9 AS Numeric(18, 1)), CAST(2.9 AS Numeric(18, 1)), CAST(3.9 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-19' AS Date), CAST(1.0 AS Numeric(18, 1)), CAST(2.0 AS Numeric(18, 1)), CAST(3.0 AS Numeric(18, 1)), CAST(4.0 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-18' AS Date), CAST(1.1 AS Numeric(18, 1)), CAST(2.1 AS Numeric(18, 1)), CAST(3.1 AS Numeric(18, 1)), CAST(4.1 AS Numeric(18, 1)))
GO