[SQL]用 APPLY 來簡化 SQL

2015-04-14

3300
0
SQL

本文章介紹使用 APPLY 來减少重覆的字算式

今天看到「Using APPLY to make your queries DRYer」蠻有意思，所以來跟大家分享。

之前跟同事分享SQL時，都會請同事一定要把SQL的執行順序記下來，如下，

1.From
2.Where
3.Group By
4.Having
5.Select
6.Order By

所以想要在 Group By 中使用 Select 裡的運算式，就必須在 Group By 中再寫一次。

如果程式複雜的話，那SQL就會變的不好維護，這時我們可以將這個運算式放到 APPLY 裡面包成衍生資料表。

先來看 Gerald Britton 如何把 Select 裡的運算式，讓 APPLY 包成衍生資料表，如下，


SELECT dt, dt_year
FROM (VALUES (GETDATE())) cur(dt) 
-- 把上面的 cur 放到 APPLY 之中 包成另一個 衍生資料表
CROSS APPLY (SELECT YEAR(cur.dt) as dt_year ) cur_year;

以 AdventureWorksDW 的範例來看，


SELECT  CASE [Model]
          WHEN 'Mountain-100' THEN 'M200'
          WHEN 'Road-150' THEN 'R250'
          WHEN 'Road-650' THEN 'R750'
          WHEN 'Touring-1000' THEN 'T1000'
          ELSE LEFT([Model], 1) + RIGHT([Model], 3)
        END + ' ' + [Region] AS [ModelRegion] ,
        ( CONVERT(INTEGER, [CalendarYear]) * 100 ) + CONVERT(INTEGER, [Month]) AS [TimeIndex] ,
        SUM([Quantity]) AS [Quantity] ,
        SUM([Amount]) AS [Amount] ,
        CalendarYear ,
        [Month] ,
        [dbo].[udfBuildISO8601Date]([CalendarYear], [Month], 25) AS ReportingDate
FROM    [dbo].[vDMPrep]
WHERE   [Model] IN ( 'Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
                     'Road-650', 'Road-750', 'Touring-1000' )
GROUP BY CASE [Model]
           WHEN 'Mountain-100' THEN 'M200'
           WHEN 'Road-150' THEN 'R250'
           WHEN 'Road-650' THEN 'R750'
           WHEN 'Touring-1000' THEN 'T1000'
           ELSE LEFT(Model, 1) + RIGHT(Model, 3)
         END + ' ' + [Region] ,
        ( CONVERT(INTEGER, [CalendarYear]) * 100 ) + CONVERT(INTEGER, [Month]) ,
        CalendarYear ,
        [Month] ,
        [dbo].[udfBuildISO8601Date]([CalendarYear], [Month], 25);

可以發現 [ModelRegion], [TimeIndex] 及 ReportingDate 的運算式，在 SELECT 及 GROUP BY 都各出現一次。

所以我們可以將它們放到 APPLY 裡面包成衍生資料表，然後給個 Table 別名 (apy)，如下，


SELECT  [ModelRegion] ,
        [TimeIndex] ,
        SUM([Quantity]) AS [Quantity] ,
        SUM([Amount]) AS [Amount] ,
        CalendarYear ,
        [Month] ,
        ReportingDate
FROM    [dbo].[vDMPrep]
        CROSS APPLY ( SELECT    CASE [Model]
                                  WHEN 'Mountain-100' THEN 'M200'
                                  WHEN 'Road-150' THEN 'R250'
                                  WHEN 'Road-650' THEN 'R750'
                                  WHEN 'Touring-1000' THEN 'T1000'
                                  ELSE LEFT([Model], 1) + RIGHT([Model], 3)
                                END + ' ' + [Region] AS [ModelRegion] ,
                                ( CONVERT(INTEGER, [CalendarYear]) * 100 )
                                + CONVERT(INTEGER, [Month]) AS [TimeIndex] ,
                                [dbo].[udfBuildISO8601Date]([CalendarYear],
                                                            [Month], 25) AS ReportingDate
                    ) apy
WHERE   [Model] IN ( 'Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
                     'Road-650', 'Road-750', 'Touring-1000' )
GROUP BY [ModelRegion] ,
        [TimeIndex] ,
        CalendarYear ,
        [Month] ,
        ReportingDate;

這樣運算式只在 APPLY 中出現一次，就不會重覆出現了哦!