计算日期和计数

Question

我有一个很大的SQL请求，其中我计算日期或计数(从其他表)，并且我必须根据那些预先计算的日期和计数的条件来计算新的日期。

在下面的示例中，我计算comp_nactive和comp_date_last_completed，并使用它们计算comp_date_next_todo。

SELECT
    pms_id,
    (
        SELECT
            COUNT(DISTINCT date_assigned)
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
              AND date_completed IS NULL
    ) AS comp_nactive,
    (
        SELECT
            CONVERT( DATE, MAX(date_completed))
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
    ) AS comp_date_last_completed,
    CONVERT( DATE, date_first_todo) AS date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM pms outer_pms

到目前为止，我找到的唯一解决方案是复制/粘贴代码，因为我不能在其余的请求中使用comp_nactive (例如)。虽然它很管用，但它很难看，而且很难管理。

我想有可能变得更干净更聪明。有什么暗示吗？

我希望尽可能地避免函数，因为我并不总是拥有创建这些函数的授权。如果可能的话，代码应该同时工作在SQL Server和Oracle上，因为我需要这两种类型的DB。小数据集：

CREATE TABLE pms
([pms_id] varchar(9), [date_first_todo] datetime, [interval] int, [interval_type] varchar(4));

INSERT INTO pms ([pms_id], [date_first_todo], [interval], [interval_type])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', 1, 'm'),
('CHANGE-1Y', '2017-02-06 01:00:00', 1, 'yyyy');

CREATE TABLE wrhwr
([pms_id] varchar(8), [date_assigned] datetime, [date_completed] datetime);

INSERT INTO wrhwr ([pms_id], [date_assigned], [date_completed])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', '2017-01-07 01:00:00'),
('CHECK-1M', '2017-02-05 01:00:00', '2017-02-13 01:00:00'),
('CHECK-1M', '2017-03-05 01:00:00', NULL);

预期产出：

CHECK-1M    1       2016-02-13      2017-01-05      NULL
CHANGE-1Y   0       NULL            2017-02-06      2017-02-06

Answer 1

鉴于你的样本数据，这是我可以得出的袖口。

虽然这可能不是一个最佳的解决方案，但它应该是朝着正确方向迈出的一步，我相信你可以从这里开始。我也做了一些关于无效性的假设，因为在你的样本集中没有明确地提供这一点。因此，您可以相应地缩减或修改comp_date_next_todo定义，因为会进行一些额外的空检查。

SELECT
    outer_pms.pms_id,
    incmp_wrhwr.cnt_date_assigned AS comp_nactive,
    oa_wrhwr.comp_date_last_completed ,
    CONVERT(DATE, date_first_todo) AS date_first_todo, 
 CASE WHEN oa_wrhwr.comp_date_last_completed IS NULL
    AND date_first_todo IS NOT NULL 
    AND interval IS NOT NULL THEN
        -- dateLastCompleted == null
        CASE WHEN interval_type = 'd'   AND DATEADD(d, interval, date_first_todo)       > GETDATE() THEN CONVERT(DATE, date_first_todo)                                 
             WHEN interval_type = 'ww'  AND DATEADD(ww, interval, date_first_todo)      > GETDATE() THEN CONVERT(DATE, date_first_todo)
             WHEN interval_type = 'm'   AND DATEADD(m, interval, date_first_todo)       > GETDATE() THEN CONVERT(DATE, date_first_todo)             
             WHEN interval_type = 'q'   AND DATEADD(q, interval, date_first_todo)       > GETDATE() THEN CONVERT(DATE, date_first_todo)                 
             WHEN interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)             
        ELSE CASE 
                WHEN cnt_date_assigned = 0 THEN CONVERT(DATE, GETDATE())
                WHEN cnt_date_assigned > 0 THEN CONVERT(DATE, date_first_todo)
            END
        END 
    END AS comp_date_next_todo
FROM pms outer_pms
OUTER APPLY (SELECT  CONVERT( DATE, MAX(date_completed)) comp_date_last_completed 
            FROM wrhwr 
            WHERE wrhwr.pms_id = outer_pms.pms_id) oa_wrhwr
OUTER APPLY (SELECT COUNT(date_assigned) cnt_date_assigned
            FROM wrhwr WHERE wrhwr.pms_id = outer_pms.pms_id AND date_completed IS NULL) incmp_wrhwr;

这里要提出的唯一真正要点是，OUTER APPLYs替换了我们多余的子查询，并将我们的情况分解为一个更容易维护和阅读的东西，根据我所理解的需求。不幸的是，DATEADD将不接受第一个参数的字符串，否则我们可能会进一步折叠。希望这能有所帮助！

编辑:这是另一种解决方案，它使用OR来实现可读性，并将cnt_date_assigned >0的检查移到主情况下。但是，对于Server 2008 R2，执行计划是相同的，因此您应该使用对您和您的团队来说更易于维护和更易读的内容。我把它作为实现同一目标的不同手段的一个例子。:)

CASE WHEN oa_wrhwr.comp_date_last_completed IS NULL
    AND date_first_todo IS NOT NULL 
    AND interval IS NOT NULL         
   THEN CASE WHEN (
                    (interval_type = 'd'   AND DATEADD(d, interval, date_first_todo) > GETDATE())                                 
                    OR (interval_type = 'ww'  AND DATEADD(ww, interval, date_first_todo) > GETDATE())     
                    OR (interval_type = 'm'   AND DATEADD(m, interval, date_first_todo) > GETDATE())                
                    OR (interval_type = 'q'   AND DATEADD(q, interval, date_first_todo) > GETDATE())                   
                    OR (interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) > GETDATE())
                    OR cnt_date_assigned > 0
                )
                THEN CONVERT(DATE, date_first_todo)    
        WHEN cnt_date_assigned = 0 
                THEN CONVERT(DATE, GETDATE())            
    END 
END AS comp_date_next_todo

编辑2:最后一次，保证！我个人更喜欢这个版本而不是另外两个版本。

CASE WHEN oa_wrhwr.comp_date_last_completed IS NULL
    AND date_first_todo IS NOT NULL 
    AND interval IS NOT NULL 
THEN CASE WHEN 
        (CASE WHEN interval_type = 'd'   THEN DATEADD(d, interval, date_first_todo)     
                WHEN interval_type = 'ww'  THEN DATEADD(ww, interval, date_first_todo) 
                WHEN interval_type = 'm'   THEN DATEADD(m, interval, date_first_todo)            
                WHEN interval_type = 'q'   THEN DATEADD(q, interval, date_first_todo)               
                WHEN interval_type = 'yyyy' THEN  DATEADD(yyyy, interval, date_first_todo) END)   > GETDATE()
        OR cnt_date_assigned > 0
    THEN CONVERT(DATE, date_first_todo)             
    WHEN cnt_date_assigned = 0 
            THEN CONVERT(DATE, GETDATE())            
    END 
END AS comp_date_next_todo

Answer 2

由于日期/时间函数的使用通常在DBMSes之间不同，所以您总是必须重构代码，但是Server和Oracle的CTE-语法是相同的。此外，您可以简单地使用派生表重写:选择.来自(选择.)

第一步是使用CTE编写重复的Scalar Subqueries一次：

WITH cte AS 
 (
   SELECT
      pms_id,
      interval_type, interval,
      (
          SELECT
              Count(DISTINCT date_assigned)
          FROM wrhwr
          WHERE pms_id = outer_pms.pms_id
                AND date_completed IS NULL
      ) AS comp_nactive,
      (
          SELECT
              CONVERT( DATE, MAX(date_completed))
          FROM wrhwr
          WHERE pms_id = outer_pms.pms_id
      ) AS comp_date_last_completed,
      CONVERT( DATE, date_first_todo) AS date_first_todo
   FROM pms outer_pms
 )
SELECT 
   pms_id,
   comp_nactive,
   comp_date_last_completed,
   date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d, INTERVAL, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww, INTERVAL, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww, INTERVAL, date_first_todo)   <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww, INTERVAL, date_first_todo)   <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m, INTERVAL, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q, INTERVAL, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM cte;

而且，由于两个Scalar子查询都以类似的方式(只是附加条件)访问同一个表，因此可以在加入之前将它们重写为简单的聚合：

WITH cte AS 
 (
   SELECT
      pms_id,
      Count(DISTINCT CASE WHEN date_completed IS NULL THEN date_assigned END) AS comp_nactive,
      CONVERT( DATE, Max(date_completed)) AS comp_date_last_completed
   FROM wrhwr 
   GROUP BY pms_id
 )
SELECT 
   pms.pms_id,
   Coalesce(comp_nactive, 0) as comp_nactive,
   comp_date_last_completed,
   date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d,    INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww,   INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww,   INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww,   INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m,    INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q,    INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM pms LEFT JOIN cte -- must be an Outer Join
  ON pms.pms_id = cte.pms_id

您只需处理为comp_nactive应用COALESCE返回的NULL即可。

见雷克斯试验器上的小提琴