LoginSignup
1
1
@ymgc3inTIS株式会社

データ分析SQLのコーディング規約

Posted at

データ分析SQLのコーディング規約

文字に関する規約

  • 予約語/関数は、大文字にする。
    • SQL自体は大文字小文字を区別しないが、予約語や関数を大文字にすることで可読性が向上する。
  • JOINのASに単文字を使わない。（変数名も）
    • 単文字だとテーブルの意味が分かりにくくなるため、明確な別名をつける。
  • スネークケースで書く。
    • 他のデータベースとの互換性のため、スネークケースを使用する。
  • 接頭辞(prefix) / 接尾辞(suffix)をつける。
    • 集計関数や時間単位などを接頭辞・接尾辞で明示する。
  • idというカラム名は使わない。
    • 結合ミスを防ぐため、他のデータベースから読み込む際にカラム名を変更する。

体裁に関する規約

  • 1行に1カラムにする。
    • 複数カラムを1行に書くと分かりにくくなるため、1行1カラムを徹底する。
  • インデントは2文字（スペース）にする。
    • 分析クエリは横長になりがちなので、2スペースのインデントを使う。
  • AND,ORは改行して先頭に配置する。
    • 条件式が複数になる場合、改行して視認性を高める。
  • SELECT単位でインデントを揃える。
    • JOINが多い分析SQLでは、SELECT単位でインデントを揃えて階層を明確にする。

機能的規約

  • INNER JOINを使わない。
    • データ除外の危険性があるため、LEFT JOINとWHERE句を使って丁寧に処理する。
  • 一定の量が増えたらサブクエリをWITH句にする。
    • サブクエリが深くなりすぎないよう、3階層程度でWITH句を使う。
  • 集計時刻のカラムを作る。
    • データマート作成時にCURRENT_TIMESTAMP()で集計時刻を記録する。
  • 集計期間には、ファンクション変数を使う。
    • 日時変数は関数化し、変更に強いクエリにする。

例外

  • UNIONする場合は、上記の規約に従わなくてもよい。
    • 縦型から横型に変換する際など、可読性のために規約を変更してもよい。

まとめ

以上が、データ分析SQLのコーディング規約をまとめたものです。分析SQLは可読性とエラー防止が特に重要なので、これらの規約を意識してコーディングすることが大切です。ただし、状況に応じて柔軟に対応することも必要です。

データ分析SQLのコーディング規約

文字に関する規約

  • 予約語/関数は、大文字にする。
    • SQL自体は大文字小文字を区別しないが、予約語や関数を大文字にすることで可読性が向上する。

Bad:❌

select 
  max(created_at) 
from
  users
where
 id = 1;

Good:✅

SELECT
  MAX(created_at)
FROM
  users
WHERE
  id = 1;
  • JOINのASに単文字を使わない。（変数名も）
    • 単文字だとテーブルの意味が分かりにくくなるため、明確な別名をつける。

Bad:❌

SELECT 
  u.name,
  p.product_name 
FROM
 users AS u
JOIN 
 purchases AS p ON u.id = p.user_id;

Good:✅

SELECT
  users.name,
  purchases.product_name
FROM
  users
JOIN 
  purchases ON users.id = purchases.user_id;
  • スネークケースで書く。
    • 他のデータベースとの互換性のため、スネークケースを使用する。

Bad:❌

SELECT 
  userId,
  createdAt 
FROM
  users;

Good:✅

SELECT
  user_id,
  created_at
FROM
  users;
  • 接頭辞(prefix) / 接尾辞(suffix)をつける。
    • 集計関数や時間単位などを接頭辞・接尾辞で明示する。

Bad:❌

SELECT 
  COUNT(*) AS count,
  SUM(price) AS sum
FROM
  orders;

Good:✅

SELECT
  COUNT(*) AS total_orders,
  SUM(price) AS total_revenue
FROM
  orders;
  • idというカラム名は使わない。
    • 結合ミスを防ぐため、他のデータベースから読み込む際にカラム名を変更する。

Bad:❌

SELECT
  users.id,
  orders.i
FROM
  users
JOIN 
  orders ON users.id = orders.user_id;

Good:✅

SELECT 
  users.user_id,
  orders.order_id 
FROM
  users
JOIN
  orders ON users.user_id = orders.user_id;

体裁に関する規約

  • 1行に1カラムにする。
    • 複数カラムを1行に書くと分かりにくくなるため、1行1カラムを徹底する。

Bad:❌

SELECT user_id, created_at, updated_at FROM users;

Good:✅

SELECT
  user_id,
  created_at,
  updated_at
FROM
  users;
  • インデントは2文字（スペース）にする。
    • 分析クエリは横長になりがちなので、2スペースのインデントを使う。

Bad:❌

SELECT
    user_id,
    COUNT(*) AS total_orders
FROM
    orders
GROUP BY
    user_id;

Good:✅

SELECT
  user_id,
  COUNT(*) AS total_orders
FROM
  orders
GROUP BY
  user_id;
  • AND,ORは改行して先頭に配置する。
    • 条件式が複数になる場合、改行して視認性を高める。

Bad:❌

SELECT * 
FROM
  users 
WHERE
  created_at >= '2022-01-01' AND created_at < '2023-01-01' AND status = 'active';

Good:✅

SELECT *
FROM 
  users
WHERE
  created_at >= '2022-01-01'
  AND created_at < '2023-01-01'
  AND status = 'active';
  • SELECT単位でインデントを揃える。
    • JOINが多い分析SQLでは、SELECT単位でインデントを揃えて階層を明確にする。

Bad:❌

SELECT users.name, COUNT(orders.id) AS total_orders
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE users.created_at >= '2022-01-01'
GROUP BY users.id;

Good:✅

SELECT
  users.name,
  COUNT(orders.id) AS total_orders
FROM
  users
LEFT JOIN
  orders
    ON users.id = orders.user_id
WHERE
  users.created_at >= '2022-01-01'
GROUP BY
  users.id;

機能的規約

  • INNER JOINを使わない。
    • データ除外の危険性があるため、LEFT JOINとWHERE句を使って丁寧に処理する。

Bad:❌

SELECT
  users.name,
  COUNT(orders.id) AS total_orders
FROM
  users
INNER JOIN
  orders
    ON users.id = orders.user_id
GROUP BY
  users.id;

Good:✅

SELECT
  users.name,
  COUNT(orders.id) AS total_orders
FROM
  users
LEFT JOIN
  orders
    ON users.id = orders.user_id
WHERE
  orders.id IS NOT NULL
GROUP BY
  users.id;
  • 一定の量が増えたらサブクエリをWITH句にする。
    • サブクエリが深くなりすぎないよう、3階層程度でWITH句を使う。

Bad:❌

SELECT
  user_id,
  total_orders
FROM
  (
    SELECT
      user_id,
      COUNT(*) AS total_orders
    FROM
      (
        SELECT
          user_id,
          order_id
        FROM
          orders
        WHERE
          created_at >= '2022-01-01'
      )
    GROUP BY
      user_id
  );

Good:✅

WITH orders_filtered AS (
  SELECT
    user_id,
    order_id
  FROM
    orders
  WHERE
    created_at >= '2022-01-01'
)
SELECT
  user_id,
  COUNT(*) AS total_orders
FROM
  orders_filtered
GROUP BY
  user_id;
  • 集計時刻のカラムを作る。
    • データマート作成時にCURRENT_TIMESTAMP()で集計時刻を記録する。

Bad:❌

SELECT
  user_id,
  COUNT(*) AS total_orders
FROM
  orders
GROUP BY
  user_id;

Good:✅

SELECT
  user_id,
  COUNT(*) AS total_orders,
  CURRENT_TIMESTAMP() AS aggregated_at
FROM
  orders
GROUP BY
  user_id;
  • 集計期間には、ファンクション変数を使う。
    • 日時変数は関数化し、変更に強いクエリにする。

Bad:❌

SELECT
  user_id,
  COUNT(*) AS total_orders
FROM
  orders
WHERE
  created_at >= '2022-01-01'
  AND created_at < '2023-01-01'
GROUP BY
  user_id;

Good:✅

CREATE TEMPORARY FUNCTION start_date() AS (DATE '2022-01-01');
CREATE TEMPORARY FUNCTION end_date() AS (DATE '2023-01-01');

SELECT
  user_id,
  COUNT(*) AS total_orders
FROM
  orders
WHERE
  created_at >= start_date()
  AND created_at < end_date()
GROUP BY
  user_id;

例外

  • UNIONする場合は、上記の規約に従わなくてもよい。
    • 縦型から横型に変換する際など、可読性のために規約を変更してもよい。

Example:

SELECT
  user_id,
  'order_count' AS metric,
  COUNT(*) AS value
FROM
  orders
GROUP BY
  user_id

UNION ALL

SELECT
  user_id,
  'total_revenue' AS metric,
  SUM(total_amount) AS value
FROM
  orders
GROUP BY
  user_id;

以上が、データ分析SQLのコーディング規約をまとめたものです。各項目についてGood/Badの例を追加し、規約の意図をより明確に示しました。これらの例を参考に、可読性と保守性の高いSQLを書くことを心がけましょう。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1