SQLのヒントとコツ

 (github.com/ben-n93)
7 ポイント 投稿者 GN⁺ 2024-09-26 | 2件のコメント | WhatsAppで共有

目次

フォーマット/可読性

  • フィールドを区切るときは先頭カンマを使う
  • WHERE句でダミー値を使う
  • 適切にコードをインデントする
  • 複雑なクエリを書くときはCTEを検討する

便利な機能

  • :: 演算子を使ってデータ型を変換する
  • アンチ結合を活用する
  • ウィンドウ関数のフィルタリングに QUALIFY を使う
  • 列位置で GROUP BY を使える

避けるべき落とし穴

  • NULL 値と一緒に NOT IN を使う際は注意する
  • 計算済みフィールドの名前を変えて曖昧さを防ぐ
  • 各列がどのテーブルに属するかを明示する
  • 実行順序を理解する
  • コードにコメントを付ける
  • ドキュメント全体を読む

フォーマット/可読性

フィールドを区切るときは先頭カンマを使う

  • SELECT 句でフィールドを区切る際に先頭カンマを使うと、新しい列を明確に区別できる
  • 先頭カンマは、カンマの抜け漏れを簡単に確認できる視覚的な手がかりになる
SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

WHERE句でダミー値を使う

  • WHERE句でダミー値を使うことで、条件を動的に追加・削除できる
SELECT *
FROM employees
WHERE 1=1 -- ダミー値
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

適切にコードをインデントする

  • コードをインデントすると可読性が上がり、同僚や将来の自分にとって理解しやすくなる
-- 悪い例:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- 良い例:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

複雑なクエリを書くときはCTEを検討する

  • インラインビューをネストする代わりに共通テーブル式（CTE）を使うことで、コードの可読性と整理しやすさを高められる
-- インラインビューを使用:
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- CTEを使用:
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

便利な機能

:: 演算子を使ってデータ型を変換する

  • 一部のRDBMSでは、:: 演算子を使って値を別のデータ型に変換できる
SELECT CAST('5' AS INTEGER); -- CAST関数を使用
SELECT '5'::INTEGER; -- :: 構文を使用

アンチ結合を活用する

  • アンチ結合は、片方のテーブルにしか存在しない行を返したいときにとても便利
  • サブクエリも使えるが、一般的にはアンチ結合のほうが速い
-- アンチ結合:
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- サブクエリ:
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- 相関サブクエリ:
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

ウィンドウ関数のフィルタリングに QUALIFY を使う

  • QUALIFY を使うと、ウィンドウ関数の結果をフィルタリングできる
  • コード行数を減らすのに便利
-- QUALIFYを使用:
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- QUALIFYなし:
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

列位置で GROUP BY を使える

  • 列名の代わりに列位置を使って GROUP BY や ORDER BY を書ける
  • 一時的なクエリでは便利だが、本番コードでは常に列名を参照すべき
SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_noはSELECT句の1番目の列
ORDER BY 2 DESC;

避けるべき落とし穴

NULL 値と一緒に NOT IN を使う際は注意する

  • NOT IN は NULL 値があると正しく動作しない
  • 代わりに NOT EXISTS を使うべき
INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- NULL値のため動作しない
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- 解決方法
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

計算済みフィールドの名前を変えて曖昧さを防ぐ

  • 計算済みフィールドに既存の列名を付けると、予期しない動作が起こることがある
INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- ウィンドウ関数が 'Robot' 製品を1位にランク付けする
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

各列がどのテーブルに属するかを明示する

  • 複雑なクエリでは、各列がどのテーブルに属するかを明示すると問題を追いやすい
SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

実行順序を理解する

  • SQLを学ぶ人にとって最も重要なアドバイスは、実行順序を理解すること
  • 実行順序を理解すると、クエリの書き方が大きく変わる

コードにコメントを付ける

  • コードを書くときは、理由を説明するコメントを付けるべき
  • 同僚や将来の自分が感謝するはず
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- 新しいCMSはアーカイブ動画形式を処理できない
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

ドキュメント全体を読む

  • ドキュメントを一通り読むと、思わぬ問題を避けられる
  • 読むのに数分しかかからず、予期しない問題の解決に役立つ
-- ドキュメントをもっと読んでいればNULL問題を解決できた
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- GREATEST_IGNORE_NULLS関数を使える
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

GN⁺のまとめ

  • この記事は、SQLをより効率的かつ読みやすく書くためのさまざまなヒントとテクニックを紹介している
  • SQLを初めて学ぶ人にも、経験のあるデータアナリストにも役立つ情報が多い
  • 特に複雑なクエリを書くときのCTE活用、アンチ結合、QUALIFY の利用などは実務で大いに役立つはず
  • SQLの実行順序を理解し、コードにコメントを付け、ドキュメントを丁寧に読む習慣を付けることが重要
  • 似た機能を持つ別のツールとしては、PostgreSQL、MySQL、Oracle などがある

関連記事

2件のコメント

 
hiyama 2024-09-26

先頭カンマがこの投稿ではすべて末尾カンマで書かれていますね。原文では先頭カンマで入力されています。

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt; 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt;= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;
 
GN⁺ 2024-09-26
Hacker Newsの意見

  • DBサーバーをよく理解し、クエリプランを頻繁に確認する必要性

    • EXISTS が IN より速いことが多い
    • NOT EXISTS と EXCEPT は NULL 値の処理で異なる動作をする
    • テーブル結合の代わりにサブクエリのカラム使用を推奨
    • テーブルスキャンを避け、インデックスを追加すべき
    • 式によるフィルタリングでは、計算列とインデックスを使用できる
    • UNION ALL が OR より速い場合がある
    • サブクエリ JOIN によってフィルタリング順序を強制できる

  • 複雑なストアドプロシージャ作業のコツ

    • 永続テーブルを一時テーブルにコピーし、必要な行だけをフィルタリングする
    • 一時テーブルを操作する
    • トランザクション内で永続テーブルを更新し、エラー時はロールバックする
    • リモートテーブル作業時は注意が必要で、一時テーブルにコピーしてから作業することを推奨
    • クエリプランが複雑になることがあるため、小さな段階に分けて作業する
    • クエリプランを常に確認する

  • コードの可読性に関する意見

    • 最初の2つの例は可読性を犠牲にして書きやすさを高めている
    • 最後の例はインデントが大きな効果を与えていない

  • SQLのFROM-firstとパイピング構文の使用提案

    • Kusto query language を使った経験は大きな進歩だった

  • Anti Joinに関するヒント

    • EXISTS の使用を推奨。条件ベースのサブクエリで行の存在有無を確認する際に有利

  • SELECT 文で先頭カンマを使う利点

    • 個別の行をコメントアウトできる
    • コードのインデントで可読性が向上する

  • MSSQLでコメントを使う際は -- ではなく /* */ の使用を推奨

    • クエリストアが改行なしでクエリを保存するため

  • ウィンドウ関数の使用を推奨

  • WHERE 句で 1=1 を使うことへの論争

  • AI2sqlの紹介

    • 平文の英語プロンプトでSQLクエリを生成できる
    • 複雑なクエリ作成時に役立つ