複数列 - sql 集計関数

SQL Server-相関付きの条件付き集計 (2)

LATERAL SQL標準にはこのような制限はありません。 CROSS APPLYは、Microsoftのベンダー固有の拡張機能です（オラクルは互換性のために後で採用しました）。また、MSの機能が標準よりも先行しているため、その制限は明らかにANSI SQL標準に依拠していません。

ANSI SQLによるLATERALは、基本的に、結合ツリーの横方向参照を可能にするための結合の修飾子です。参照可能な列の数に制限はありません。

奇妙な制限が始まる理由はわかりません。多分、 CROSS APPLYはもともとテーブル値関数を可能にすることを意図していたので、それは後でサブSELECTを可能にするように拡張されたからです。

Postgresのマニュアルでは、 LATERALについてLATERALように説明しています。

LATERALキーワードは、サブSELECT FROM項目の前にLATERALことができます。これにより、サブSELECTはFROMリストの前に現れるFROM項目の列を参照することができます。（ LATERALないLATERAL 、各サブSELECTは個別に評価されるため、他のFROM項目を相互参照することはできません）。

Postgresバージョンのクエリ（より洗練されたウィンドウ関数を使用しない）は簡単になります：

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;

Postgres 9.4+には条件付き集約のためのよりエレガントな集合FILTERがあります。
~~NULLIFは冗長でした。~~ ~~revenueはNOT NULLと定義され、集計は1つ以上の行を検出することが保証され、 LATERALサブSELECTはCROSS JOINため、 sum_totalはNULLできません。~~ それは後ろ向きで、私はCOALESCEを考えました。 NULLIFは理にかなっていますが、マイナーな簡素化しか示唆していません。
sum_totalをnumericキャストしnumeric 。
あなたの望む結果に合うように丸めてください。

バックグラウンド：

元のケースは非常に簡単でした。最も高い収入から最も低い収入までユーザーあたりの稼働合計を計算する：

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                  ║
╚════╩═══════╩═════════╩════════════╩════════════════════╝

特定の窓関数を使用して異なる計算を行うことができます。

さて、ウィンドウ化されたSUMを使用してそれを書き直すことはできないと仮定しましょう。

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

SELECT CRUMsリストの相関サブクエリが気に入らず、 c3が2回使用されているので、 CROSS APPLY使用しました。

すべてが必要なように動作します。しかし、我々が近づくと、 c2とc3は非常に似ています。ですから、それらを組み合わせて単純な条件付き集計を使用してみましょう。

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

Postgresqlバージョン。唯一の違いはCROSS APPLYではなくLATERALです。

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;

SQLFiddleDemo-SQLite SQLFiddleDemo-MySQL

私は外部参照と集計を読みました：

制限のソースはSQL-92標準にあり、 SQL ServerはSybaseコードベースからSQL Server継承しました。問題は、SQL Serverが集計を計算するクエリを把握する必要があることです。

私はそれを回避する方法だけを示す回答は検索しません。

質問は次のとおりです。

標準のどの部分がそれを許さない、または妨げているのですか？
なぜ他のRDBMSにこのような外部依存性に問題がないのでしょうか？
SQL Standardを拡張してSQL Server動作させるか、 SQL Serverが完全に実装していないか（正しくは？）

私は次の文献への参照に非常に感謝しています：

ISO standard （92またはそれ以上）
SQL Server標準のサポート
これを説明するRDBMS（ SQL Server/Postgresql/Oracle/... ）からの公式の文書SQL Server/Postgresql/Oracle/... 。

編集：

私は、 SQL-92はLATERAL概念がないことを知っていSQL-92 。しかし、サブクエリを持つバージョン（ SQLite/MySQLような）はうまくSQLite/MySQLません。

LiveDemo

編集2：

これを単純化するため、相関サブクエリのみをチェックしてみましょう：

SELECT c.Customer, c."User", c.Revenue,
       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;

上記のバージョンは、 MySQL/SQLite/Postgresqlうまく動作しMySQL/SQLite/Postgresql 。

SQL Serverエラーが発生します。それを1つのレベルに「平坦化」するためにサブクエリをラップした後、それは機能します：

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

この質問のポイントは、 SQL standardがどのようにそれを規制しているかです。

LiveDemo

簡単な解決策があります：

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年前にかなり記憶していましたが、私はその特定の制限を思い出しません。

私は注意する必要があります：

SQL92標準の時点では、横方向結合は実際にはレーダー上にはありませんでした。 Sybaseは間違いなくそのような概念を持っていなかった。
他のデータベースには外部参照に問題があります。特に、スコープの深さを1レベルに制限することがよくあります。
SQL標準自体は、実際のデータベース・ユーザーの要件によって駆動されるのではなく、高度に政治的（つまりベンダー主導型）である傾向があります。まあ、時間がたつにつれて、それは正しい方向に動きます。