SQL server 2008 不支持LAG函数

潇锐killer

已于 2024-04-08 09:53:37 修改

阅读量590

点赞数 3

CC 4.0 BY-SA版权

文章标签：数据库 sql mysql

于 2024-04-02 08:53:14 首次发布

本文链接：https://blog.youkuaiyun.com/wangqiaowq/article/details/137255555

本文介绍了如何在SQLServer中使用子查询和CTE（CommonTableExpression）进行数据处理，尤其是在金融数据中计算年月增长率。同时，提到了子查询在大数据集上的潜在性能问题和替代方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用子查询（Subquery）

WITH CTE AS (
    SELECT 
        RowID,
        Value,
        ROW_NUMBER() OVER (ORDER BY RowID) AS RowNum
    FROM 
        YourTable
)
SELECT 
    c1.RowID,
    c1.Value,
    c2.Value AS PrevValue
FROM 
    CTE c1
LEFT JOIN 
    CTE c2 ON c1.RowNum = c2.RowNum + 1;

WITH CTE AS (

SELECT RowID, Value, ROW_NUMBER() OVER (ORDER BY RowID) AS RowNum FROM YourTable )

SELECT c1.RowID, c1.Value, c2.Value AS PrevValue

FROM CTE c1

LEFT JOIN CTE c2

ON c1.RowNum = c2.RowNum + 1;

在某些特定场景下，也可以通过嵌套查询或窗口函数的前身——ROW_NUMBER()函数配合子查询来实现类似效果。例如，为每一行分配一个行号，然后在外部查询中引用这个行号来获取前一行的数据：

这里创建了一个名为CTE的公共表表达式，其中包含了原始表的行ID、值以及基于RowID排序后的行号。然后通过外部查询将当前行与行号相差1的行进行连接，从而得到前一行的值。

子查询方法在SQL Server 2008中较为通用，但可能会在大数据集上导致性能下降。

具体修改例子如下：

DECLARE @Sql43 NVARCHAR(MAX) = N'
WITH financeAvgMoney AS (
       SELECT
               t2.[year],
               t2.quarter,
               t2.month,
               t2.account_set_ord,
               t2.account_set_title,
               t2.month_money,
               t2_prev.year AS prev_year,
               t2_prev.month_money AS same_month_last_year_money,
               ROW_NUMBER() OVER (
                   PARTITION BY t2.[year], t2.quarter, t2.month, t2.account_set_ord, t2.account_set_title
                   ORDER BY t2.[year], t2.quarter, t2.month, t2.account_set_ord, t2.account_set_title
               ) AS rn
           FROM (
               SELECT
                   year,
                   quarter,
                   month,
                   account_set_ord,
                   account_set_title,
                   month_money
               FROM (
                   SELECT
                       year,
                       quarter,
                       CAST(SUBSTRING(ym, 6, 2) AS INT) AS month,
                       account_set_ord,
                       account_set_title,
                       avg_money AS month_money
                   FROM dws_finance_avg_money_month
                   WHERE dt = ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N'
               ) t1
           ) t2
           LEFT JOIN (
               SELECT
                   year,
                   quarter,
                   month,
                   account_set_ord,
                   account_set_title,
                   month_money
               FROM (
                   SELECT
                       year,
                       quarter,
                       CAST(SUBSTRING(ym, 6, 2) AS INT) AS month,
                       account_set_ord,
                       account_set_title,
                       avg_money AS month_money
                   FROM dws_finance_avg_money_month
                   WHERE dt = DATEADD(year, -1, ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N')
               ) t1_prev
           ) t2_prev
           ON t2.year = t2_prev.year + 1
           AND t2.quarter = t2_prev.quarter
           AND t2.month = t2_prev.month
           AND t2.account_set_ord = t2_prev.account_set_ord
           AND t2.account_set_title = t2_prev.account_set_title
       ),
       months AS (
           SELECT DISTINCT [year],quarter, month,account_set_ord, account_set_title FROM financeAvgMoney
       )
       INSERT INTO dbo.ads_finance_avg_money_yoy_mom_month ([dt],[year],[quarter],[ym],[account_set_ord],[account_set_title],[avg_money],[avg_previous_month_money],[avg_same_month_last_year_money],[yoy_growth_rate],[mom_growth_rate])
       select
           ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' as dt,
           year,
           quarter,
           CAST(year AS VARCHAR(4)) + ''-'' + RIGHT(''00'' + CAST(month AS VARCHAR(2)), 2) AS ym,
           account_set_ord,
           account_set_title,
           month_money,
           previous_month_money,
           same_month_last_year_money,
           CASE WHEN same_month_last_year_money = 0 THEN null ELSE ((month_money - same_month_last_year_money) / same_month_last_year_money) * 100 END AS yoy_growth_rate,
           CASE WHEN previous_month_money = 0 THEN null ELSE ((month_money - previous_month_money) / previous_month_money) * 100 END AS mom_growth_rate
       from (
           SELECT
               DISTINCT q.[year],
               q.quarter,
               q.[month],
               q.account_set_ord,
               q.account_set_title,
               case when s.[month_money] is NULL then 0 else s.[month_money] end AS month_money,
               case when sqs.[month_money] is NULL then 0 else sqs.[month_money] end AS same_month_last_year_money,
               t4.previous_month_money
           FROM months q
           JOIN financeAvgMoney s ON q.[year] = s.[year] AND q.[month] = s.[month] AND s.rn = 1
           LEFT JOIN financeAvgMoney sqs ON q.[year] - 1 = sqs.[year] AND q.[month] = sqs.[month] AND sqs.rn = 1
           left join (
               select t2.dt,
                   ym,
                   account_set_ord,
                   account_set_title,
                   t2.year,
                   t2.month,
                   t2.month_money,
                   case when monthdiff = 1 then previous_month_money else 0 end previous_month_money
               from(select dt,
               year,
                       quarter,
                       ym,
                       account_set_ord,
                       account_set_title,
                       CAST(SUBSTRING(ym, 6, 2) AS INT) AS month,
                       previous_month,
                       month_money,
                       previous_month_money,
                       DATEDIFF(MONTH, CAST((CAST(previous_month AS VARCHAR) + ''-01'') AS DATE), CAST((CAST(ym AS VARCHAR) + ''-01'') AS DATE)) AS monthdiff
                   from(SELECT
                           t1.dt,
                           t1.year,
                           t1.quarter,
                           t1.ym,
                           t1.account_set_ord,
                           t1.account_set_title,
                           case when t1_prev.ym is NULL then ''1970-01'' else t1_prev.ym end AS previous_month,
                           case when t1_prev.avg_money is NULL then 0 else t1_prev.avg_money end AS previous_month_money,
                           t1.avg_money AS month_money
                       FROM dws_finance_avg_money_month t1
                       LEFT JOIN dws_finance_avg_money_month t1_prev
                       ON t1.dt = t1_prev.dt
                       AND t1.account_set_ord = t1_prev.account_set_ord
                       AND t1.account_set_title = t1_prev.account_set_title
                       AND t1.ym > t1_prev.ym
                       WHERE t1.dt = ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N'
                       AND NOT EXISTS (
                           SELECT 1
                           FROM dws_finance_avg_money_month t2
                           WHERE t2.dt = t1.dt
                           AND t2.account_set_ord = t1.account_set_ord
                           AND t2.account_set_title = t1.account_set_title
                           AND t2.ym > t1_prev.ym
                           AND t2.ym < t1.ym
                       )) t1) t2
           ) t4 on q.year = t4.year and q.month = t4.month
       ) t1'

   SET @Sql43 = REPLACE(@Sql43, '@DateThreshold', @DateThreshold)
   print(@Sql43)
   EXEC sp_executesql @Sql43