初心者のためのSQL操作の真の順序のガイド

SQL言語は非常に直感的です。 そうでなくなるまでは。

長年にわたり、多くの人がさまざまな理由で SQL 言語を批判してきました。 たとえば IDE は提供する自動補完オプションを容易に推測できない。FROM 節を指定しない限り、(まだ)スコープ内にテーブルは存在しないからだ。

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

これらのことは、操作の語彙的順序が論理的順序と一致しないため、奇妙なことなのです。 私たち人間は、この順序の違いを直感的に理解することがある(ことが多い)。 例えば、私たちは顧客テーブルから選択しようとしていることを知っています。

GROUP BY が最も混乱させる要因

若い開発者や SQL 初心者が SQL を使い始めると、かなり早く集約と GROUP BY について知ることになります。 そして、

SELECT count(*)FROM customer

やった、顧客が 200 人もいる!

このようなことをすぐに書いてしまうでしょう。

そして、

SELECT count(*)FROM customerWHERE first_name = 'Steve'

うわー、そのうちの 90 人はスティーブと呼ばれている! 面白いですね。 名前ごとに何人いるか調べてみましょう…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Very nice. でも、全部同じなのでしょうか? 姓も調べてみよう

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

おっと!

ORA-00979: not a GROUP BY expression

じーさん、これはどういう意味? (注意、残念ながら、STRICT モードを使用していない MySQL ユーザーはここでまだ任意の姓の結果を得る!だから新しい MySQL ユーザーは自分の間違いが理解できないだろう)

どうやって SQL 初心者にこれを簡単に説明するかね? プロ」にとっては当たり前のことのようですが、本当に当たり前のことなのでしょうか。 後輩に簡単に説明できるほど自明なことなのでしょうか? 考えてみてください。 なぜ、これらのステートメントのそれぞれが意味的に正しいのか、間違っているのか。

問題は構文に関連している

SQL構文は英語と同じように機能します。 それはコマンドです。 コマンドは動詞で始めます。 動詞は SELECT (または INSERTUPDATEDELETECREATEDROP など)

残念ながら、人間の言語はプログラミングのはるかにフォーマルな世界には信じられないほど不向きです。 全くの初心者である新しいユーザー (おそらく非プログラマー) にはある程度の慰めを与えますが、それ以外の人にとっては物事を難しくしてしまうだけです。 すべての異なるSQL句は、非常に複雑な相互依存性を持っています。 たとえば、

  • GROUP BY 節がある場合、GROUP BY 式 (またはその関数依存関係) から構築された式、または集約関数だけが HAVING, SELECT, ORDER BY 節で使用できます。
  • 単純化のため、GROUPING SETS について話すことさえやめておきますが
  • 実際、GROUP BY が暗示される場合がいくつか存在します。 例えば 裸の」 HAVING 節を書いた場合
  • SELECT 節にある単一の集約関数は、(GROUP BY がない場合)単一の行への集約を強制します
  • 実際、これは ORDER BY にその集約関数を置くことによっても(何らかの理由で)示唆されます
  • あなたは FROM 節からの任意の列を SELECT せずに参照するかなりの数の式がORDER BY 可能になります。 しかし、SELECT DISTINCT

と書くと、それはもはや真ではありません。 もし興味があれば、標準SQLドキュメントを読んで、SELECT文の多くの句の間にどれだけ奇妙で複雑な相互依存関係が存在するかをチェックしてみてください。

これは理解できるでしょうか。 これは、私の SQL マスター クラスを訪れる参加者にいつも説明している簡単なトリックです。 SQL 操作 (節) の語彙的 (構文的) な順序は、操作の論理的順序とはまったく一致しません (ただし、偶然に一致することもあります)。 最近のオプティマイザーのおかげで、この順序は実際の操作の順序とも一致しません。したがって、実際には、構文上の順序 -> 論理上の順序 -> 実際の順序となりますが、今はそれは置いておくことにしましょう。

論理的な操作順序は次のとおりです(「単純化」のため、CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOTなどのベンダー固有のものは省いています):

  • FROM:これは実際に、論理的に最初に起こることです。 何よりもまず、すべてのテーブルからすべての行をロードし、それらを結合しているのです。 悲鳴をあげて怒る前に 繰り返しますが、これは論理的に最初に起こることで、実際に起こることではありません。 オプティマイザはおそらくこの操作を最初に行わず、WHERE句に基づくインデックスにアクセスします。 しかし、ここでも論理的にはこの操作が最初に行われます。 また、すべてのJOIN句は実際にはこのFROM句の一部です。 JOINは関係代数における演算子である。 ちょうど+-が算術の演算子であるように。 SELECTFROM
  • WHERE のような独立した節ではありません。 上のテーブルからすべての行を読み込んだら、今度は WHERE
  • GROUP BY: 必要に応じて、WHERE の後に残った行をグループまたはバケットに入れ、各グループに GROUP BY 式と同じ値を入れることができます(他のすべての行はそのグループのリストに入れられます)。 Javaでは、次のようなものになります。 Map<String, List<Row>>. GROUP BY 節を指定した場合、実際の行にはグループの列だけが含まれ、残りの列はリストに含まれなくなります。 リスト内のこれらの列は、そのリストに対して操作できる集約関数によってのみ表示されます。
  • 集約を参照してください。 これは理解することが重要です。 構文的にどこに集約関数を置いたとしても(つまり、SELECT節の中、またはORDER BY節の中)、ここが集約関数が計算されるステップになるのです。 GROUP BY のすぐ後です。 (注意: 論理的に。 賢いデータベースは、実は以前から計算しているかもしれません)。 このことから、WHERE節に集約関数を置くことができないのは、その値がまだアクセスできないからであることがわかります。 WHERE句は論理的には集計の前に置かれる。 集約関数は、上記の各グループの「このリスト」に入れたカラムにアクセスすることができます。 集計後、「this list」は消えて利用できなくなります。 GROUP BY 節がない場合、すべての行を含む、キーのない 1 つの大きなグループが存在することになります。 例えば、HAVING句のcount(*) > 1を確認することができます。 HAVINGGROUP BY の後なので(または GROUP BY を暗示するので)、GROUP BY 列ではなかった列や式にアクセスできなくなりました。
  • WINDOW: すごい窓関数機能を使っているなら、これはすべて計算されるステップです。 今だけです。 そして、素晴らしいことに、すでにすべての集約関数が(論理的に!)計算されているので、集約関数をウィンドウ関数にネストすることができるのです。 したがって、sum(count(*)) OVER ()row_number() OVER (ORDER BY count(*))のように書いても全く問題ありません。 ウィンドウ関数が今だけ論理的に計算されるということは、ウィンドウ関数を SELECT 節または ORDER BY 節にのみ置くことができる理由も説明することができます。 以前は、WHERE節では使用できませんでした。 PostgreSQLとSybase SQL Anywhereには、実際にWINDOW句があることに注意してください!
  • SELECT: 最後に これで、上記の句から生成されるすべての行を使用し、SELECT を使用してそれらから新しい行/タプルを作成することができます。 計算したすべてのウィンドウ関数、計算したすべての集約関数、指定したすべてのグループ化列、またはグループ化や集約を行わなかった場合は、FROM句のすべての列を使用することができるのです。 覚えておいてください。 SELECT の内部で集計しているように見えても、これはずっと前に行われたことで、甘い甘い count(*) 関数は結果への参照に過ぎません。
  • DISTINCT: はい! DISTINCT は、SELECT の列リストの前に置かれたとしても、構文的には SELECT の後に起こります。 しかし、考えてみてください。 これは完全に理にかなっています。 もしまだすべての行 (とその列) を知らないのであれば、他にどのように異なる行を削除することができるでしょうか? UNION は 2 つのサブクエリを接続する演算子です。 これまで話してきたことはすべてサブクエリでした。 結合の出力は、最初のサブクエリと同じ行型(つまり同じ列)を含む新しいクエリになります。 通常は なぜなら、変人オラクルでは、カラム名を定義するのは、最後から2番目のサブクエリで正しいからです。 Oracleデータベース、構文トロール 😉
  • ORDER BY: 結果の順序付けの決定を最後まで延期することは全く意味があります。なぜなら、他のすべての操作は内部的に、ハッシュマップを使うかもしれないので、途中の順序は再び失われるかもしれないからです。 というわけで、結果の順序付けができるようになりました。 通常、ORDER BY節からは、SELECTで指定しなかった行(または式)を含む多くの行にアクセスすることができます。 しかし、以前は、DISTINCTを指定すると、選択されていない行/式での順序付けができなくなりました。 なぜでしょうか。
  • OFFSET のように、順序がかなり不定になってしまうからです。 オフセットを使用しない
  • LIMIT, FETCH, TOP: 現在、まともなデータベースでは、LIMIT (MySQL, PostgreSQL) または FETCH (DB2, Oracle 12c, SQL Server 2012) 節を構文上、一番最後に記述しています。 昔、Sybase と SQL Server は TOPSELECT のキーワードとして持つことが良いアイデアだと考えていました。 まるでSELECT DISTINCTの正しい順序がすでに十分に混乱していなかったかのように。

ほら、できたよ。 これは完全に意味をなしています。 そして、「正しい順序」でないことをしたい場合、最も簡単なトリックは、常に派生テーブルに頼ることです。 例: ウィンドウ関数でグループ化したい場合:

なぜうまくいくのでしょうか?

  • 派生テーブルでは、まずFROMが起こり、次にWINDOWが計算され、バケットがSELECTされるからです。
  • 外側のSELECTは、この窓関数計算の結果をFROM節で普通のテーブルと同じように扱い、次にGROUP BY普通の列、次に集約、そしてSELECT

なぜうまくいくか、なぜうまくいかないかの説明で元の例を見直してみましょう。

常に操作の論理的順序を考える

頻繁に SQL を書く人でない場合、構文は確かに混乱する可能性があります。 特にGROUP BYと集約は、残りのSELECT節全体に「感染」してしまい、本当におかしなことになってしまいます。 この奇妙さに直面したとき、私たちには2つの選択肢があります。

  • Get mad and scream at the SQL language designers
  • Accept our fate, close our eyes, forget about the snytax and remember the logical operations order

I generally recommend the latter, because then things start a lot more sense, which nests the daily revenue (SUM(amount) aggregate function) inside the cumulative revenue (SUM(...) OVER (...) window function)以下の美しい累積日収計算など。

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… 集計は論理的にウィンドウ関数の前に行われるからです。

Caveat: ORDER BY 節

ORDER BY 節の周りにはいくつかの注意点があり、それがさらなる混乱の原因になっている可能性があります。 デフォルトでは、論理的な操作の順序が正しいと仮定して続けます。 しかし、その後、特にいくつかの特殊なケースがあります:

  • DISTINCT 節がない場合 UNION

のようにセット操作がない場合 ORDER BY で式を参照できますが、SELECT では投影されません。 次のクエリは、ほとんどのデータベースでまったく問題なく実行できます。 これは非常に便利であるが、セマンティクスと操作の順序について若干の混乱を招く可能性がある。 具体的には、このような状況では、例えば DISTINCT は使用できません。 次のクエリは無効です:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

なぜならば、同じ名前で非常に異なる ID を持つ 2 つのアクターが存在する場合はどうでしょうか。

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

この場合、ACTOR_ID 列は CUSTOMER テーブルに存在しないため、クエリーはまったく意味をなしません。

  • SQL GROUP BY と関数依存性。 非常に便利な機能
  • How SQL GROUP BY Should Have Been Designed – Like Neo4j’s Implicit GROUP BY
  • How to Translate SQL GROUP BY and Aggregations to Java 8
  • Do You Really Understand SQL’s GROUP BY and HAVING cluses?
  • GROUP BY ROLLUP / CUBE

コメントを残す

メールアドレスが公開されることはありません。