による画像 Freepik

SQL (Structured Query Language) は、データの管理と操作に使用されるプログラミング言語です。そのため、SQL クエリは、構造化された効率的な方法でデータベースと対話するために非常に重要です。

SQL でのグループ化は、データを整理および分析するための強力なツールとして機能します。複雑なデータセットから有意義な洞察と要約を抽出するのに役立ちます。グループ化の最適な使用例は、データの特性を要約して理解することにより、企業の分析およびレポートのタスクを支援することです。

一般に、グループ内の統計を計算するために、共通のデータによってデータセット レコードを結合する必要があるという多くの要件があります。これらのインスタンスのほとんどは、一般的なシナリオに一般化できます。これらのシナリオは、同様の種類の要件が発生したときにいつでも適用できます。

SQL の GROUP BY 句は次の目的で使用されます。

1. いくつかの列のデータをグループ化する
2. グループを単一行に減らす
3. グループの他の列に対して集計操作を実行します。

グループ化列 = グループ化列の値はグループ内のすべての行で同じである必要があります

集計列 = 集計列の値は通常、sum、max などの関数が適用される値によって異なります。

集計列をグループ化列にすることはできません。

シナリオ 1: グループ化して合計の合計を求める

売上テーブル内のすべてのカテゴリの合計売上を計算したいとします。

そこで、カテゴリごとにグループ化し、カテゴリごとの個別の売上を集計します。

select category, 
sum(amount) as sales
from sales
group by category;

グループ化列 = カテゴリ

集計列＝金額

集計関数 = sum()

シナリオ 2: カウントを見つけるためのグループ化

各部門の従業員数を計算したいとします。

この場合、部門ごとにグループ化し、すべての部門の従業員数を計算します。

select department, 
count(empid) as emp_count
from employees
group by department;

グループ化列 = 部門

集計列 = empid

集計関数 = カウント

シナリオ 3: 平均を見つけるためのグループ化

各部門の従業員の平均給与を計算したいとします。

同様に、もう一度部門ごとにグループ化し、各部門の従業員の平均給与を個別に計算します。

select department, 
avg(salary) as avg_salary
from employees
group by department;

グループ化列 = 部門

集計列＝給与

集計関数 = 平均

シナリオ 4: 最大値/最小値を見つけるためのグループ化

各部門の従業員の最高給与を計算したいとします。

部門をグループ化し、部門ごとの最高給与を計算します。

select department, 
max(salary) as max_salary
from employees
group by department;

グループ化列 = 部門

集計列＝給与

集計関数 = 最大

シナリオ 5: 重複を見つけるためのグループ化

データベース内で重複または同じ顧客名を検索したいとします。

顧客名でグループ化し、集計関数として count を使用します。さらに、集計関数に対して句を使用して、1 より大きいカウントのみをフィルタリングします。

select name, 
count(*) AS duplicate_count
from customers
group by name
having count(*) > 1;

グループ化列 = 名前

集計列 = *

集計関数 = カウント

Hasing = 集計関数に適用されるフィルター条件

SQL の PARTITION BY 句は次の目的で使用されます。

1. 一部の列でのデータのグループ化/パーティション化
2. 個々の行は保持され、 一つに結合された
3. グループ/パーティションの他の列に対してランキング操作と集計操作を実行します。

列のパーティション分割 = データをグループ化する列を選択します。パーティション列のデータは各グループで同じである必要があります。指定しない場合、テーブル全体が単一のパーティションとみなされます。

順序付け列 = パーティション化列に基づいて作成された各グループで、グループ内の行を順序付け/並べ替えます。

ランキング関数 = ランキング関数または集計関数がパーティション内の行に適用されます。

シナリオ 6: グループ内で最高のレコードを見つけるためのパーティショニング

すべてのカテゴリのどの本が最も売れているのか、またそのトップセラーの本の売り上げを計算したいとします。

この場合、group by 句は使用できません。グループ化すると、すべてのカテゴリのレコードが 1 つの行に減らされるためです。

ただし、各カテゴリでどの書籍が最も売れたのかを確認するには、書籍名、金額などのレコードの詳細とカテゴリが必要です。

select book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales;

分割列 = カテゴリ

注文欄＝金額

ランキング関数 = row_number()

このクエリでは book_sales テーブル内のすべての行が得られ、行はすべての書籍カテゴリごとに並べられており、最も売れている書籍が行番号 1 になります。

各カテゴリで最も売れている書籍を取得するには、行番号 1 の行のみをフィルタリングする必要があります。

select category, book_name, amount from (
select category, book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales
) as book_ranked_sales
where sales_rank = 1;

上記のフィルターを使用すると、各カテゴリのトップセラーの書籍と、各トップセラーの書籍の販売額のみが表示されます。

シナリオ 7: グループ内の累積合計を求めるためのパーティショニング

販売された売上の累計 (累計) を計算したいとします。製品ごとに個別の累計が必要です。

product_id でパーティションを分割し、日付でパーティションを並べ替えます。

select product_id, date, amount,
sum(amount) over (partition by product_id order by date desc) as running_total
from sales_data;

パーティショニング列 = product_id

順序列 = 日付

ランキング関数 = sum()

シナリオ 8: グループ内の値を比較するためのパーティショニング

すべての従業員の給与をその部門の平均給与と比較したいとします。

そこで、部門に基づいて従業員を分割し、各部門の平均給与を求めます。

さらに、従業員の個人給与から平均を簡単に差し引いて、従業員の給与が平均より高いか低いかを計算することができます。

select employee_id, salary, department,
avg(salary) over (partition by department) as avg_dept_sal
from employees;

パーティション化列 = 部門

順序列 = 順序なし

ランキング関数 = avg()

シナリオ 9: 結果を等しいグループに分割するためのパーティショニング

給与に基づいて従業員を 4 つの等しい (またはほぼ等しい) グループに分割したいとします。

したがって、従業員の各グループの数値 ID を持つ別の論理列 tile_id を派生します。

グループは給与に基づいて作成されます。最初のタイル グループの給与が最も高くなります。以下同様です。

select employee_id, salary,
ntile(4) over (order by salary desc) as tile_id
from employees;

パーティション化された列 = パーティションなし - 完全なテーブルが同じパーティション内にあります

順序列 = 給与

ランキング関数 = ntile()

シナリオ 10: データ内のアイランドまたはギャップを特定するためのパーティショニング

連続した product_id 列があり、この列のギャップを特定したいとします。

したがって、product_id が連続している場合は同じ番号を持つ別の論理列 island_id を導出します。 product_id でブレークが識別されると、island_id がインクリメントされます。

select product_id,
row_number() over (order by product_id) as row_num,
product_id - row_number() over (order by product_id) as island_id,
from products;

パーティション化された列 = パーティションなし - 完全なテーブルが同じパーティション内にあります

注文列 = product_id

ランキング関数 = row_number()

Group By と Partition By は、次のような多くの問題を解決するために使用されます。

情報の要約: グループ化すると、データを集約し、グループごとに情報を要約することができます。

パターンの分析: データ サブセット内のパターンや傾向を特定するのに役立ち、データセットのさまざまな側面についての洞察が得られます。

統計分析： グループ内の平均、カウント、最大値、最小値、その他の集計関数などの統計的尺度の計算を有効にします。

データクレンジング： グループ内の重複、不一致、または異常を特定するのに役立ち、データのクレンジングと品質向上をより管理しやすくします。

コホート分析: コホートベースの分析、エンティティのグループの経時的な追跡と比較などに役立ちます。
 
 

Hanu 実行します ヘルパーコードのブログ 主に扱うのは SQL チートシート。私はフルスタック開発者で、再利用可能なアセットの作成に興味があります。

• SEO を活用したコンテンツと PR 配信。 今日増幅されます。
• PlatoData.Network 垂直生成 Ai。 自分自身に力を与えましょう。 こちらからアクセスしてください。
• プラトアイストリーム。 Web3 インテリジェンス。 知識増幅。 こちらからアクセスしてください。
• プラトンESG。 カーボン、 クリーンテック、 エネルギー、 環境、 太陽、 廃棄物管理。 こちらからアクセスしてください。
• プラトンヘルス。 バイオテクノロジーと臨床試験のインテリジェンス。 こちらからアクセスしてください。
• 情報源： https://www.kdnuggets.com/sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science?utm_source=rss&utm_medium=rss&utm_campaign=sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science