时间戳

首页 > 解决方案 > 如何在 SQL Server 中使用 Pivot？

sql-server - 如何在 SQL Server 中使用 Pivot？

问题描述

我有两个表，列如下：

CUSTOMER表为DIM_CUSTOMER：

ID_CUSTOMER, CUSTOMER_NAME

TRANSACTION表为FACT_TRANSACTION：

ID_CUSTOMER, DATE, TOTAL_PRICE, QUANTITY

问题陈述是

查找前 100 名客户及其平均支出、每年的平均数量。还可以找到他们支出变化的百分比。

我的做法：

SELECT TOP 100 
    YEAR(FT.DATE) AS [YEAR],
    FT.ID_CUSTOMER AS [CUSTOMER NAME],
    FT.TOTAL_PRICE AS [TOTAL AMT],
    AVG(FT.TOTAL_PRICE) AS [AVG SPEND],
    AVG(FT.QUANTITY) AS [AVG QUANTITY]
FROM 
    FACT_TRANSACTIONS FT
INNER JOIN 
    DIM_CUSTOMER DC ON FT.ID_CUSTOMER = DC.ID_CUSTOMER
GROUP BY 
    FT.DATE, FT.ID_CUSTOMER, FT.TOTAL_PRICE
ORDER BY 
    3 DESC

这导致根据使用情况排名前 100 位的客户。

现在我需要确定他们在年度支出中的百分比变化。

我怎样才能做到这一点？可能在这里使用 PIVOT 选项会有所帮助，但我不确定。

标签： sql-servertsqlpivot

解决方案

您可以尝试使用LAG来访问[AVG SPEND]当前行的前一个。这个想法是对每个[CUSTOMER NAME]使用的数据进行分组PARTITION BY，然后按[YEAR]. 该函数将为我们提供先前的结果，我们可以轻松计算出差异。

尝试这样的事情：

SELECT TOP 100 
    YEAR(FT.DATE) AS [YEAR],
    FT.ID_CUSTOMER AS [CUSTOMER NAME],
    FT.TOTAL_PRICE AS [TOTAL AMT],
    AVG(FT.TOTAL_PRICE) AS [AVG SPEND],
    AVG(FT.QUANTITY) AS [AVG QUANTITY]
INTO #DataSource
FROM 
    FACT_TRANSACTIONS FT
INNER JOIN 
    DIM_CUSTOMER DC ON FT.ID_CUSTOMER = DC.ID_CUSTOMER
GROUP BY 
    YEAR(FT.DATE), FT.ID_CUSTOMER, FT.TOTAL_PRICE
ORDER BY 
    [AVG SPEND] DESC 

SELECT *
      ,[AVG SPEND] - LAG([AVG SPEND], 1, 0) OVER (PARTITION BY [CUSTOMER NAME] ORDER BY [YEAR])
FROM #DataSource

注意：

  • 该功能需要 SQL Server 2012+
  • 您可以根据需要更改分区和排序以满足您的实际目标（例如，您可以使用ORDER BY [YEAR] DESC
  • LEAD如果您想计算提前的差异，您可以使用该函数来访问组中的下一个值
  • 我在临时表中具体化了数据，但是您可以使用表变量或您正在使用的任何东西

推荐阅读