SQL :ボスのように
11208 ワード
そこでヘレンの偉大なポストを読んだ後、
私は、私がポストまたは2にdevに貢献するべきであると決めました、そこで、ここでは、私の最初の努力に従います.そして、SQLの不足している特徴を見ます.
まず、免責事項.SQL言語はISO standard (9075) , しかし、多くの“標準”と同様にいくつかの実装は完全に標準に準拠しています.いくつかのデータベースシステムは、いくつかの機能を省略します.これは、マイクロソフトSQL Server、Oracle、PostgreSQL、Presto/AWSアテナのような一般的なデータベースプラットフォームの多くに適用可能です.
SQLグループ
私の52週の筋：良い、悪い、醜い
ヘレンアンダーソン・ 月2日・ 5分読む
#devjournal
#motivation
#writing
#showdev
私は、私がポストまたは2にdevに貢献するべきであると決めました、そこで、ここでは、私の最初の努力に従います.そして、SQLの不足している特徴を見ます.
まず、免責事項.SQL言語はISO standard (9075) , しかし、多くの“標準”と同様にいくつかの実装は完全に標準に準拠しています.いくつかのデータベースシステムは、いくつかの機能を省略します.これは、マイクロソフトSQL Server、Oracle、PostgreSQL、Presto/AWSアテナのような一般的なデータベースプラットフォームの多くに適用可能です.
SQLグループ
単純なSELECT * FROM <table>; SQLステートメントは、テーブルに格納されているデータの列とデータ列が概念的に一致する形式のテーブルからデータの行を返します.データベースで動作するアプリケーションを設計するときは、通常、このようなデータを使用して、表示の行を問い合わせるか、新しい情報を行を追加または更新する必要があります.
ただし、SQLを使用して解析するときは、テーブルのすべての行に情報を求めたくない場合があります.たとえば、列の1つの値の合計または平均を取得するには、集計します.
SELECT SUM(UnitsSold) AS TotalUnitsSold
FROM Orders;
全額売買
550
すごい!だから今ではテーブル全体の集計値があります.しかし、より現実的に、我々はテーブルのためにちょうど合計を望みません、我々は他のコラム(例えば3)の1つで各々の値のために完全に壊れて欲しいですSupplier . したがって、次のようにします.
SELECT Supplier, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY Supplier;
供給元
全額売買
チョコレートファクトリー
100
トロッター独立取引
200
KWK - Eマート
250
私たちはGROUP BY データベースに伝えるSQL文の節SUM の値UnitsSold の各一意の値の列Supplier .
グループを2つ以上の列で拡張することができますので、グループ化された列の値の各ユニークな組み合わせの合計を取得します.
SELECT Supplier, City, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY Supplier, City;
供給元
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
いいね.今、我々は迅速なレポートやデータ抽出の基礎を持っている.
しかし、どのように多くのユニットが“トロッター独立取引”を販売したか?今、我々はグループ化の複数のレベルを追加しました(サプライヤー-シティ)我々は再びグループ化の高いレベルの合計を得るためにそれらを追加する必要があります.
Tableau、Power BiまたはMicroStrategyなどのレポートまたは視覚化ツールにこの質問を置くつもりであるならば、これらのツールがデータが表示されるとき、あなたのために合計を計算するために構成されることができるので、あなたの仕事はおそらくされます.
しかし、もしあなたがすぐにデータベースからいくつかの答えを取得しようとしている(上司は、これらの月額販売台数を望んでいる!)その後、いずれかのExcelのように別のツールにデータをエクスポートするつもりだ、または恐怖の恐怖は、手動で結果を追加するには、自分で合計を計算する(私はそれを見てきた).あなたがもう少し自信があるならば、あなたは一緒に彼らを追加しているグループ化の異なるレベルで複数のSQL声明を作成しようとするかもしれませんUNION ALL .
ロールラップ、ロールプ！サーカスのグループは町にある
パニックしないでください、SQLはしばしば見落とされたログープとキューブ節であなたの正気を保存するためにここにある.基本的に、これらのSQLステートメント節を使用すると、データベースは、あなたのために重いリフティングを行い、結果の同じセット内の合計とサブ集計を行うことができます.
我々の例に戻って、我々が各々のためにサブSupplier また、すべての注文のための全体的な合計ので、我々が追加されますROLLUP 我々にGROUP BY 条項
SELECT Supplier, City, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY ROLLUP(Supplier, City);
供給元
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
チョコレートファクトリー
NULL
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
トロッター独立取引
NULL
200
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
KWK - Eマート
NULL
250
NULL
NULL
550
我々は現在、それぞれのための余分な行を持っている通知Supplier , とNULL 値City カラム.これは、各サプライヤーのために販売されているすべてのユニティの合計を表します.また、下には、行を持っているNULL サプライヤーと都市の両方の値は、これはすべての供給元とすべての都市(すなわち、全テーブルの合計)のために販売されている合計unitsSs -これは我々が書いた最初の簡単な合計クエリから私たちの合計と一致することに注意してください.
それで？
さて、我々が現在我々の合計を持っているという点で、それは大きいですNULL 値は、これを少し読むのを難しくします(そして、あなたがこれを示すどんな非SQL人々にでも混乱させます).幸いにもほとんどのデータベースプラットフォームもグループ化と呼ばれる機能を(申し訳ありませんプレスト/アテナのユーザーは、あなたはこの1つの不運なキャンプにしている).この関数は列名を受け取り、1(true)または0(false)を返します.GROUPING(Supplier) 行がサプライヤーのサブ合計を含んでいるか、通常の行なら0を返します.
このグループ化機能を行うことのできることの一つは、私たちの結果をNULL もう少し意味のある値
SELECT
CASE
WHEN GROUPING(Supplier) = 1
THEN '-All Suppliers-'
ELSE Supplier
END AS Supplier
, CASE
WHEN GROUPING(City) = 1
THEN '-All Cities-'
ELSE City
END AS City
, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY ROLLUP(Supplier, City);
供給元
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
チョコレートファクトリー
-すべての都市
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
トロッター独立取引
-すべての都市
200
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
KWK - Eマート
-すべての都市
250
-すべてのサプライヤー
-すべての都市
550
今それははるかに読みやすいです!時間は上司とニップダウン祝賀パイントのパブに番号をメールする.
待って、あなたはキューブについて何か言及しなかった？
ああはい、キューブ.正直に言うと、立方体のユースケースはrolulupの場合よりも格段に優れているが、それについてはそれを知っておくのが良い.
キューブは、基本的にステロイドにrollup - rollupは、グループ化された列の順序に基づいてグループ化の各サブレベルのためのサブ合計を作成するSupplier それから、「すべての行」City それがグループ化の「底」レベルであるので.
他の手のキューブは、グループ化された列の値のすべての組み合わせのためのサブ集計を作成します.
これはどうですか.もう一度例を見てみましょう.今までのところ、これらの例は、供給者と都市の間に自然な階層があると仮定していました、多くの都市(1～多くのまたは1:N関係)への1つのサプライヤーです、しかし、1つの供給元が多くの都市を持つことができるように、そして、1つの都市が多くの供給元(多くの多くの、または、M:N関係)に関連することができるように、これらは実は順序の独立した特質です.その場合、我々はちょうどサプライヤーのためにサブ物を欲しくありません、我々は同様に各々の都市のためにサブ物を望みます.
単に置換ROLLUP with CUBE SQL文では、この結果を取得します.
SELECT
CASE
WHEN GROUPING(Supplier) = 1
THEN '-All Suppliers-'
ELSE Supplier
END AS Supplier
, CASE
WHEN GROUPING(City) = 1
THEN '-All Cities-'
ELSE City
END AS City
, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY CUBE(Supplier, City);
供給元
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
チョコレートファクトリー
-すべての都市
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
トロッター独立取引
-すべての都市
200
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
KWK - Eマート
-すべての都市
250
-すべてのサプライヤー
ミュンヘン
100
-すべてのサプライヤー
ニューヨーク
10
-すべてのサプライヤー
パリ
20
-すべてのサプライヤー
ペッカム
170
-すべてのサプライヤー
スプリングフィールド
150
-すべてのサプライヤー
シェルフビル
100
-すべてのサプライヤー
-すべての都市
550
今、私たちはそれぞれのためのサブSupplier また、それぞれの別々のサブ合計City , プラス全体の合計!
ファンタスティック！今私はすべてのクエリにrolulupを追加するつもりです！
ウーそこカウボーイ!私が簡単に述べたように、Tableauのようなレポートツールによって使用されようとしているクエリを書いているなら、これらのツールは通常、あなたのためにあなたのために全力を尽くすでしょう.実際、これは通常はanti-pattern そして、あなたの報告ツールが通常の行からの副行を区別することができないので、賢明ではありません、そして、それはあなたのレポートをもっと難しくフォーマットします.
しかし、もしあなたが直接SQLで作業しているだけで迅速なアドホックレポートや分析のためのいくつかのデータを生成したい場合は、ログープやキューブは、いくつかの時間と労力を必要に応じて保存することができます必要があります.
Reference
この問題について(SQL :ボスのように), 我々は、より多くの情報をここで見つけました
https://dev.to/griffinator76/rollup-like-a-boss-3dkl
テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol
SELECT SUM(UnitsSold) AS TotalUnitsSold
FROM Orders;
SELECT Supplier, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY Supplier;
SELECT Supplier, City, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY Supplier, City;
パニックしないでください、SQLはしばしば見落とされたログープとキューブ節であなたの正気を保存するためにここにある.基本的に、これらのSQLステートメント節を使用すると、データベースは、あなたのために重いリフティングを行い、結果の同じセット内の合計とサブ集計を行うことができます.
我々の例に戻って、我々が各々のためにサブ
Supplier また、すべての注文のための全体的な合計ので、我々が追加されますROLLUP 我々にGROUP BY 条項SELECT Supplier, City, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY ROLLUP(Supplier, City);
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
チョコレートファクトリー
NULL
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
トロッター独立取引
NULL
200
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
KWK - Eマート
NULL
250
NULL
NULL
550
我々は現在、それぞれのための余分な行を持っている通知
Supplier , とNULL 値City カラム.これは、各サプライヤーのために販売されているすべてのユニティの合計を表します.また、下には、行を持っているNULL サプライヤーと都市の両方の値は、これはすべての供給元とすべての都市(すなわち、全テーブルの合計)のために販売されている合計unitsSs -これは我々が書いた最初の簡単な合計クエリから私たちの合計と一致することに注意してください.それで？
さて、我々が現在我々の合計を持っているという点で、それは大きいですNULL 値は、これを少し読むのを難しくします(そして、あなたがこれを示すどんな非SQL人々にでも混乱させます).幸いにもほとんどのデータベースプラットフォームもグループ化と呼ばれる機能を(申し訳ありませんプレスト/アテナのユーザーは、あなたはこの1つの不運なキャンプにしている).この関数は列名を受け取り、1(true)または0(false)を返します.GROUPING(Supplier) 行がサプライヤーのサブ合計を含んでいるか、通常の行なら0を返します.
このグループ化機能を行うことのできることの一つは、私たちの結果をNULL もう少し意味のある値
SELECT
CASE
WHEN GROUPING(Supplier) = 1
THEN '-All Suppliers-'
ELSE Supplier
END AS Supplier
, CASE
WHEN GROUPING(City) = 1
THEN '-All Cities-'
ELSE City
END AS City
, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY ROLLUP(Supplier, City);
供給元
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
チョコレートファクトリー
-すべての都市
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
トロッター独立取引
-すべての都市
200
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
KWK - Eマート
-すべての都市
250
-すべてのサプライヤー
-すべての都市
550
今それははるかに読みやすいです!時間は上司とニップダウン祝賀パイントのパブに番号をメールする.
待って、あなたはキューブについて何か言及しなかった？
ああはい、キューブ.正直に言うと、立方体のユースケースはrolulupの場合よりも格段に優れているが、それについてはそれを知っておくのが良い.
キューブは、基本的にステロイドにrollup - rollupは、グループ化された列の順序に基づいてグループ化の各サブレベルのためのサブ合計を作成するSupplier それから、「すべての行」City それがグループ化の「底」レベルであるので.
他の手のキューブは、グループ化された列の値のすべての組み合わせのためのサブ集計を作成します.
これはどうですか.もう一度例を見てみましょう.今までのところ、これらの例は、供給者と都市の間に自然な階層があると仮定していました、多くの都市(1～多くのまたは1:N関係)への1つのサプライヤーです、しかし、1つの供給元が多くの都市を持つことができるように、そして、1つの都市が多くの供給元(多くの多くの、または、M:N関係)に関連することができるように、これらは実は順序の独立した特質です.その場合、我々はちょうどサプライヤーのためにサブ物を欲しくありません、我々は同様に各々の都市のためにサブ物を望みます.
単に置換ROLLUP with CUBE SQL文では、この結果を取得します.
SELECT
CASE
WHEN GROUPING(Supplier) = 1
THEN '-All Suppliers-'
ELSE Supplier
END AS Supplier
, CASE
WHEN GROUPING(City) = 1
THEN '-All Cities-'
ELSE City
END AS City
, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY CUBE(Supplier, City);
供給元
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
チョコレートファクトリー
-すべての都市
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
トロッター独立取引
-すべての都市
200
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
KWK - Eマート
-すべての都市
250
-すべてのサプライヤー
ミュンヘン
100
-すべてのサプライヤー
ニューヨーク
10
-すべてのサプライヤー
パリ
20
-すべてのサプライヤー
ペッカム
170
-すべてのサプライヤー
スプリングフィールド
150
-すべてのサプライヤー
シェルフビル
100
-すべてのサプライヤー
-すべての都市
550
今、私たちはそれぞれのためのサブSupplier また、それぞれの別々のサブ合計City , プラス全体の合計!
ファンタスティック！今私はすべてのクエリにrolulupを追加するつもりです！
ウーそこカウボーイ!私が簡単に述べたように、Tableauのようなレポートツールによって使用されようとしているクエリを書いているなら、これらのツールは通常、あなたのためにあなたのために全力を尽くすでしょう.実際、これは通常はanti-pattern そして、あなたの報告ツールが通常の行からの副行を区別することができないので、賢明ではありません、そして、それはあなたのレポートをもっと難しくフォーマットします.
しかし、もしあなたが直接SQLで作業しているだけで迅速なアドホックレポートや分析のためのいくつかのデータを生成したい場合は、ログープやキューブは、いくつかの時間と労力を必要に応じて保存することができます必要があります.
Reference
この問題について(SQL :ボスのように), 我々は、より多くの情報をここで見つけました
https://dev.to/griffinator76/rollup-like-a-boss-3dkl
テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol
SELECT
CASE
WHEN GROUPING(Supplier) = 1
THEN '-All Suppliers-'
ELSE Supplier
END AS Supplier
, CASE
WHEN GROUPING(City) = 1
THEN '-All Cities-'
ELSE City
END AS City
, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY ROLLUP(Supplier, City);
ああはい、キューブ.正直に言うと、立方体のユースケースはrolulupの場合よりも格段に優れているが、それについてはそれを知っておくのが良い.
キューブは、基本的にステロイドにrollup - rollupは、グループ化された列の順序に基づいてグループ化の各サブレベルのためのサブ合計を作成する
Supplier それから、「すべての行」City それがグループ化の「底」レベルであるので.他の手のキューブは、グループ化された列の値のすべての組み合わせのためのサブ集計を作成します.
これはどうですか.もう一度例を見てみましょう.今までのところ、これらの例は、供給者と都市の間に自然な階層があると仮定していました、多くの都市(1～多くのまたは1:N関係)への1つのサプライヤーです、しかし、1つの供給元が多くの都市を持つことができるように、そして、1つの都市が多くの供給元(多くの多くの、または、M:N関係)に関連することができるように、これらは実は順序の独立した特質です.その場合、我々はちょうどサプライヤーのためにサブ物を欲しくありません、我々は同様に各々の都市のためにサブ物を望みます.
単に置換
ROLLUP with CUBE SQL文では、この結果を取得します.SELECT
CASE
WHEN GROUPING(Supplier) = 1
THEN '-All Suppliers-'
ELSE Supplier
END AS Supplier
, CASE
WHEN GROUPING(City) = 1
THEN '-All Cities-'
ELSE City
END AS City
, SUM(UnitsSold) AS TotalUnitsSold
FROM Orders
GROUP BY CUBE(Supplier, City);
シティ
全額売買
チョコレートファクトリー
ミュンヘン
100
チョコレートファクトリー
-すべての都市
100
トロッター独立取引
ニューヨーク
10
トロッター独立取引
パリ
20
トロッター独立取引
ペッカム
170
トロッター独立取引
-すべての都市
200
KWK - Eマート
スプリングフィールド
150
KWK - Eマート
シェルフビル
100
KWK - Eマート
-すべての都市
250
-すべてのサプライヤー
ミュンヘン
100
-すべてのサプライヤー
ニューヨーク
10
-すべてのサプライヤー
パリ
20
-すべてのサプライヤー
ペッカム
170
-すべてのサプライヤー
スプリングフィールド
150
-すべてのサプライヤー
シェルフビル
100
-すべてのサプライヤー
-すべての都市
550
今、私たちはそれぞれのためのサブ
Supplier また、それぞれの別々のサブ合計City , プラス全体の合計!ファンタスティック！今私はすべてのクエリにrolulupを追加するつもりです！
ウーそこカウボーイ!私が簡単に述べたように、Tableauのようなレポートツールによって使用されようとしているクエリを書いているなら、これらのツールは通常、あなたのためにあなたのために全力を尽くすでしょう.実際、これは通常はanti-pattern そして、あなたの報告ツールが通常の行からの副行を区別することができないので、賢明ではありません、そして、それはあなたのレポートをもっと難しくフォーマットします.
しかし、もしあなたが直接SQLで作業しているだけで迅速なアドホックレポートや分析のためのいくつかのデータを生成したい場合は、ログープやキューブは、いくつかの時間と労力を必要に応じて保存することができます必要があります.
Reference
この問題について(SQL :ボスのように), 我々は、より多くの情報をここで見つけました
https://dev.to/griffinator76/rollup-like-a-boss-3dkl
テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol
Reference
この問題について(SQL :ボスのように), 我々は、より多くの情報をここで見つけました https://dev.to/griffinator76/rollup-like-a-boss-3dklテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol