SQLServer-如何创建带关联的条件聚合?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (58)

表创建如下:

CREATE TABLE t(Customer INTEGER  NOT NULL PRIMARY KEY 
              ,"User"   VARCHAR(5) NOT NULL
              ,Revenue  INTEGER  NOT NULL);

INSERT INTO t(Customer,"User",Revenue) VALUES
(001,'James',500),(002,'James',750),(003,'James',450),
(004,'Sarah',100),(005,'Sarah',500),(006,'Sarah',150),
(007,'Sarah',600),(008,'James',150),(009,'James',100);

查询:

SELECT *,
    1.0 * Revenue/SUM(Revenue) OVER(PARTITION BY "User") AS percentage,
    1.0 * SUM(Revenue) OVER(PARTITION BY "User" ORDER BY Revenue DESC)
         /SUM(Revenue) OVER(PARTITION BY "User") AS running_percentage
FROM t;

LiveDemo

输出:

╔════╦═══════╦═════════╦════════════╦════════════════════╗
║ ID ║ User  ║ Revenue ║ percentage ║ running_percentage ║
╠════╬═══════╬═════════╬════════════╬════════════════════╣
║  2 ║ James ║     750 ║ 0.38       ║ 0.38               ║
║  1 ║ James ║     500 ║ 0.26       ║ 0.64               ║
║  3 ║ James ║     450 ║ 0.23       ║ 0.87               ║
║  8 ║ James ║     150 ║ 0.08       ║ 0.95               ║
║  9 ║ James ║     100 ║ 0.05       ║ 1                  ║
║  7 ║ Sarah ║     600 ║ 0.44       ║ 0.44               ║
║  5 ║ Sarah ║     500 ║ 0.37       ║ 0.81               ║
║  6 ║ Sarah ║     150 ║ 0.11       ║ 0.93               ║
║  4 ║ Sarah ║     100 ║ 0.07       ║ 1                  ║
╚════╩═══════╩═════════╩════════════╩════════════════════╝

它可以使用特定的窗口函数进行不同的计算。

现在让我们假设我们不能使用windowedSUM并重写:

SELECT c.Customer, c."User", c."Revenue"
    ,1.0 * Revenue / NULLIF(c3.s,0) AS percentage
    ,1.0 * c2.s    / NULLIF(c3.s,0) AS running_percentage
FROM t c
CROSS APPLY
        (SELECT SUM(Revenue) AS s
        FROM t c2
        WHERE c."User" = c2."User"
            AND c2.Revenue >= c.Revenue) AS c2
CROSS APPLY
        (SELECT SUM(Revenue) AS s
        FROM t c2
        WHERE c."User" = c2."User") AS c3
ORDER BY "User", Revenue DESC;

LiveDemo

当我们仔细观察c2c3非常相似。那么,为什么不组合它们并使用简单的条件聚合:

SELECT c.Customer, c."User", c."Revenue"
    ,1.0 * Revenue        / NULLIF(c2.sum_total,0) AS percentage
    ,1.0 * c2.sum_running / NULLIF(c2.sum_total,0) AS running_percentage
FROM t c
CROSS APPLY
        (SELECT SUM(Revenue) AS sum_total,
                SUM(CASE WHEN c2.Revenue >= c.Revenue THEN Revenue ELSE 0 END) 
                AS sum_running
        FROM t c2
        WHERE c."User" = c2."User") AS c2
ORDER BY "User", Revenue DESC;

当然,我可以通过另一个子查询绕过它,但它变得有点“丑陋”:

SELECT c.Customer, c."User", c."Revenue"
    ,1.0 * Revenue        / NULLIF(c2.sum_total,0) AS percentage
    ,1.0 * c2.sum_running / NULLIF(c2.sum_total,0) AS running_percentage
FROM t c
CROSS APPLY
(   SELECT SUM(Revenue) AS sum_total,
           SUM(running_revenue) AS sum_running
     FROM (SELECT Revenue,
                  CASE WHEN c2.Revenue >= c.Revenue THEN Revenue ELSE 0 END 
                  AS running_revenue
           FROM t c2
           WHERE c."User" = c2."User") AS sub
) AS c2
ORDER BY "User", Revenue DESC

LiveDemo

SELECT c.Customer, c."User", c.Revenue
    ,1.0 * Revenue        / NULLIF(c2.sum_total,0) AS percentage 
    ,1.0 * c2.running_sum / NULLIF(c2.sum_total,0) AS running_percentage 
FROM t c
,LATERAL (SELECT SUM(Revenue) AS sum_total,
                 SUM(CASE WHEN c2.Revenue >= c.Revenue THEN c2.Revenue ELSE 0 END) 
                 AS running_sum
        FROM t c2
        WHERE c."User" = c2."User") c2
ORDER BY "User", Revenue DESC;

SqlFiddleDemo

效果很好。


SQLite/MySQL版本(这就是为什么我更喜欢LATERAL/CROSS APPLY)):

SELECT c.Customer, c."User", c.Revenue,
    1.0 * Revenue / (SELECT SUM(Revenue)
                     FROM t c2
                     WHERE c."User" = c2."User") AS percentage,
    1.0 * (SELECT SUM(CASE WHEN c2.Revenue >= c.Revenue THEN c2.Revenue ELSE 0 END)
           FROM t c2
          WHERE c."User" = c2."User")  / 
          (SELECT SUM(c2.Revenue)
           FROM t c2
           WHERE c."User" = c2."User") AS running_percentage
FROM t c
ORDER BY "User", Revenue DESC;
提问于
用户回答回答于

SELECT c.*
     , round(revenue        / c2.sum_total, 2) END AS percentage 
     , round(c2.running_sum / c2.sum_total, 2) END AS running_percentage 
FROM   t c, LATERAL (
   SELECT NULLIF(SUM(revenue), 0)::numeric AS sum_total  -- NULLIF, cast once
        , SUM(revenue) FILTER (WHERE revenue >= c.revenue) AS running_sum
   FROM   t
   WHERE  "User" = c."User"
   ) c2
ORDER  BY c."User", c.revenue DESC;
用户回答回答于

SELECT c.Customer, c."User", c."Revenue",
       1.0 * Revenue/ NULLIF(c2.sum_total, 0) AS percentage,
       1.0 * c2.sum_running / NULLIF(c2.sum_total, 0) AS running_percentage
FROM t c CROSS APPLY
     (SELECT SUM(c2.Revenue) AS sum_total,
             SUM(CASE WHEN c2.Revenue >= x.Revenue THEN c2.Revenue ELSE 0 END) 
                 as sum_running
      FROM t c2 CROSS JOIN
           (SELECT c.REVENUE) x
      WHERE c."User" = c2."User"
     ) c2
ORDER BY "User", Revenue DESC;

我不知道为什么或者是否这个限制在SQL‘92标准中。我在20多年前就有过很好的记忆,但我不记得这个特殊的限制。

扫码关注云+社区