时间戳

首页 > 解决方案 > 将公用表表达式 (CTE) 重写为子查询以制作兼容的 PowerBI DirectQuery

sql-server - 将公用表表达式 (CTE) 重写为子查询以制作兼容的 PowerBI DirectQuery

问题描述

我有以下 MS SQL Server 查询，在 PowerBI 中运行时会引发错误，因为 DirectQuery 不支持 CTE。

;WITH Daily_Report_CTE
AS
  (
    SELECT WazeUpdateTime_PST, Name, Length, Time, TimePeriod =
    (
      CASE
        WHEN DATEPART(hour, WazeUpdateTime_PST) >= 5 AND DATEPART(hour, WazeUpdateTime_PST) < 11 THEN 'Morning'
        WHEN DATEPART(hour, WazeUpdateTime_PST) >= 11 AND DATEPART(hour, WazeUpdateTime_PST) < 17 THEN 'Mid-Day'
        WHEN DATEPART(hour, WazeUpdateTime_PST) >= 17 AND DATEPART(hour, WazeUpdateTime_PST) < 23 THEN 'Evening'
        ELSE 'Overnight'
      END
     )
    FROM Repository_Archive.DataManagement_Ops_AllAccess.vw_Waze_TVT_ParsedJSON_Routes
    WHERE (DATEPART(dy, WazeUpdateTime_PST) BETWEEN DATEPART(dy,DateAdd(DD,-21,( GETDATE() AT TIME ZONE 'Pacific Standard Time'))) and DATEPART(dy,( GETDATE() AT TIME ZONE 'Pacific Standard Time')))
    AND (DATENAME(weekday, WazeUpdateTime_PST)) = DATENAME(weekday, ( GETDATE() AT TIME ZONE 'Pacific Standard Time'))
    AND Name IN ('I-105 to UL', 'La Cienega to UL', '405-La Tijera-Sepulveda to UL', 'Manchester to UL', 'Howard Hughes to UL', 'Lincoln to UL', 'Lincoln-Manchester to UL')
   )

SELECT WazeUpdateTime_PST, Name, Length, Time, TimePeriod, 
  Color = 
    (
     CASE
      WHEN Time > PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Time)  
    OVER (PARTITION BY Name, TimePeriod)
          THEN 'Red'
      WHEN Time > PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY Time)  
    OVER (PARTITION BY Name, TimePeriod)
          THEN 'Yellow'
      ELSE 'Green'
     END
     )
FROM Daily_Report_CTE

一个常见的解决方法显然是将 CTE 重写为主查询中的子查询。当谈到 SQL Server 或整个 SQL 时，我并不是最了解的，使用 CTE 构建功能查询是一项艰巨的任务。

我应该如何将其重写为子查询？

标签： sql-serversubquerycommon-table-expressionpowerbi-desktop

解决方案

在这种情况下，您只需将 CTE 移入 FROM

SELECT WazeUpdateTime_PST, Name, Length, Time, TimePeriod, 
  Color = 
    (
     CASE
      WHEN Time > PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Time)  
    OVER (PARTITION BY Name, TimePeriod)
          THEN 'Red'
      WHEN Time > PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY Time)  
    OVER (PARTITION BY Name, TimePeriod)
          THEN 'Yellow'
      ELSE 'Green'
     END
     )
FROM (
        SELECT WazeUpdateTime_PST, Name, Length, Time, TimePeriod =
            (
              CASE
                WHEN DATEPART(hour, WazeUpdateTime_PST) >= 5 AND DATEPART(hour, WazeUpdateTime_PST) < 11 THEN 'Morning'
                WHEN DATEPART(hour, WazeUpdateTime_PST) >= 11 AND DATEPART(hour, WazeUpdateTime_PST) < 17 THEN 'Mid-Day'
                WHEN DATEPART(hour, WazeUpdateTime_PST) >= 17 AND DATEPART(hour, WazeUpdateTime_PST) < 23 THEN 'Evening'
                ELSE 'Overnight'
              END
             )
            FROM Repository_Archive.DataManagement_Ops_AllAccess.vw_Waze_TVT_ParsedJSON_Routes
            WHERE (DATEPART(dy, WazeUpdateTime_PST) BETWEEN DATEPART(dy,DateAdd(DD,-21,( GETDATE() AT TIME ZONE 'Pacific Standard Time'))) and DATEPART(dy,( GETDATE() AT TIME ZONE 'Pacific Standard Time')))
            AND (DATENAME(weekday, WazeUpdateTime_PST)) = DATENAME(weekday, ( GETDATE() AT TIME ZONE 'Pacific Standard Time'))
            AND Name IN ('I-105 to UL', 'La Cienega to UL', '405-La Tijera-Sepulveda to UL', 'Manchester to UL', 'Howard Hughes to UL', 'Lincoln to UL', 'Lincoln-Manchester to UL')
     ) A

推荐阅读