SQL Serverでデータ分析のスキルを向上させる: ビジネスに役立つ10の分析関数使用例

SQLって奥が深い。

昔、ベテランの先輩同士の会話を聞いてるときに「.NETやJAVAもアレだけど、結局のところSQLが一番奥が深いよな！」って言ってました。当時はSQL覚えたてのころだったこともあり、SELECT INSERT UPDATE DELETE文の組み合わせでしょ、奥が深いわけない。なんて考えてたころもありました。今ではSQLの奥の深さに圧倒され続けてます。

そんなSQLの中で使えるようになると圧倒的に便利なのに分析関数があります。これを使えるか使えないかでSQLの組み立て方が全然違います。この記事では、中級者が次のステップへと進むための、SQL Serverの分析関数を駆使した実践的な10の使用例に合わせて紹介しますので、一緒にSQLの深さに嵌りましょう。

使用例10選

売上推移の分析
製品別パフォーマンスランキング
次期発注量の予測
顧客の購買頻度分析
四半期ごとの成長率の計算
エリア別売上のパーセンタイル分析
キャンペーン効果の前後比較
高価値顧客の特定
遅延支払いの分析
顧客別平均取引額の分析

ビジネスシーンに合わせた実例

売上推移の分析

テーブル構造: Sales (date, sales_amount)
サンプルデータ:

date	sales_amount
2023-01-01	1000
2023-01-15	1500
2023-02-01	2000
2023-02-15	1800
2023-03-01	2200
2023-03-15	2500

サンプルSQL:

SELECT 
  FORMAT(date, 'yyyy-MM') AS Month,
  SUM(sales_amount) AS TotalSales
FROM Sales
GROUP BY FORMAT(date, 'yyyy-MM')
ORDER BY Month;

SELECT

FORMAT(date, 'yyyy-MM') AS Month,

SUM(sales_amount) AS TotalSales

FROM Sales

GROUP BY FORMAT(date, 'yyyy-MM')

ORDER BY Month;

出力結果:

Month	TotalSales
2023-01	2500
2023-02	3800
2023-03	4700

分析の概要

このSQLクエリでは、SUM()関数を使用して、特定の期間（この場合は月ごと）の売上合計を計算します。SUM()関数は、指定された列の値の合計を計算するために使用され、ビジネス分析において非常に重要な役割を果たします。この例では、月ごとの売上を集計することにより、売上の時間的推移を追跡し、事業の成長トレンドや季節変動を視覚化するのに役立ちます。また、GROUP BY句と組み合わせて使用することで、特定のグループ（この場合は各月）に対する合計売上を効率的に計算することが可能になります。この分析は、経営者が売上目標の設定、マーケティング戦略の策定、資源の配分などの意思決定を行う際の基礎となります。

製品別パフォーマンスランキング

テーブル構造: Products (product_id, product_name, sales_amount)
サンプルデータ:

product_id	product_name	sales_amount
1	A	3000
2	B	1500
3	C	2000
4	D	2500

サンプルSQL:

SELECT 
  product_name,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS SalesRank
FROM Products;

SELECT

product_name,

sales_amount,

RANK() OVER (ORDER BY sales_amount DESC) AS SalesRank

FROM Products;

出力結果:

product_name	sales_amount	SalesRank
A	3000	1
D	2500	2
C	2000	3
B	1500	4

分析の概要

このクエリではRANK()関数を使用して、製品ごとの売上高に基づくランキングを生成します。RANK()関数は、結果セット内の各行に対して順位を付けることによって、特定の基準（この場合は売上高）に基づく順序付けを可能にします。この分析は、最も成功している製品を特定し、在庫管理やマーケティング戦略の計画に役立ちます。

次期発注量の予測

テーブル構造: Inventory (product_id, record_date, quantity)
サンプルデータ:

product_id	record_date	quantity
1	2023-01-01	100
1	2023-02-01	80
1	2023-03-01	60
2	2023-01-01	150
2	2023-02-01	130
2	2023-03-01	110

サンプルSQL:

SELECT 
  product_id,
  record_date,
  quantity,
  LAG(quantity, 1) OVER (PARTITION BY product_id ORDER BY record_date) AS PreviousQuantity
FROM Inventory;

SELECT

product_id,

record_date,

quantity,

LAG(quantity, 1) OVER (PARTITION BY product_id ORDER BY record_date) AS PreviousQuantity

FROM Inventory;

出力結果:

product_id	record_date	quantity	PreviousQuantity
1	2023-01-01	100	NULL
1	2023-02-01	80	100
1	2023-03-01	60	80
2	2023-01-01	150	NULL
2	2023-02-01	130	150
2	2023-03-01	110	130

分析の概要

LAG()関数を使用することで、各製品の前回の在庫数量を現在のレコードと比較することが可能になります。これにより、在庫の減少傾向を観察し、次期の発注量をより正確に予測することができます。在庫管理の効率化に寄与し、過剰在庫や在庫不足のリスクを最小化するのに役立ちます。

顧客の購買頻度分析

テーブル構造: Purchases (customer_id, purchase_date)
サンプルデータ:

customer_id	purchase_date
1	2023-01-05
1	2023-03-12
2	2023-01-25
2	2023-04-03

サンプルSQL:

SELECT
  customer_id,
  purchase_date,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) AS PurchaseOrder
FROM Purchases;

SELECT

customer_id,

purchase_date,

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) AS PurchaseOrder

FROM Purchases;

出力結果:

customer_id	purchase_date	PurchaseOrder
1	2023-01-05	1
1	2023-03-12	2
2	2023-01-25	1
2	2023-04-03	2

分析の概要

ROW_NUMBER()関数を使用して、各顧客ごとに購入履歴を順番に並べ、購買頻度を分析します。この方法では、顧客がどのようなペースで購入しているかを明確に把握でき、マーケティング戦略や顧客エンゲージメントの計画に活用できます。

四半期ごとの成長率の計算

テーブル構造: QuarterlySales (year_quarter, sales_amount)
サンプルデータ:

year_quarter	sales_amount
2023-Q1	10000
2023-Q2	10500
2023-Q3	11000

サンプルSQL:

SELECT
  year_quarter,
  sales_amount,
  LAG(sales_amount, 1) OVER (ORDER BY year_quarter) AS PreviousQuarterSales,
  (sales_amount - LAG(sales_amount, 1) OVER (ORDER BY year_quarter)) / LAG(sales_amount, 1) OVER (ORDER BY year_quarter) * 100 AS GrowthRate
FROM QuarterlySales;

SELECT

year_quarter,

sales_amount,

LAG(sales_amount, 1) OVER (ORDER BY year_quarter) AS PreviousQuarterSales,

(sales_amount - LAG(sales_amount, 1) OVER (ORDER BY year_quarter)) / LAG(sales_amount, 1) OVER (ORDER BY year_quarter) * 100 AS GrowthRate

FROM QuarterlySales;

出力結果:

year_quarter	sales_amount	PreviousQuarterSales	GrowthRate
2023-Q1	10000	NULL	NULL
2023-Q2	10500	10000	5.00
2023-Q3	11000	10500	4.76

分析の概要

この例では、LAG()関数を用いて前四半期の売上を現在の四半期の売上と比較し、成長率を計算します。この分析を通じて、事業の短期間内の成長動向を評価し、将来の戦略計画に役立つ洞察を得ることができます。

エリア別売上のパーセンタイル分析

テーブル構造: SalesByRegion (region_id, sales_amount)
サンプルデータ:

region_id	sales_amount
1	5000
1	7000
2	3000
2	4000

サンプルSQL:

SELECT
  region_id,
  sales_amount,
  PERCENT_RANK() OVER (PARTITION BY region_id ORDER BY sales_amount) AS SalesPercentile
FROM SalesByRegion;

SELECT

region_id,

sales_amount,

PERCENT_RANK() OVER (PARTITION BY region_id ORDER BY sales_amount) AS SalesPercentile

FROM SalesByRegion;

出力結果:

region_id	sales_amount	SalesPercentile
1	5000	0.0
1	7000	1.0
2	3000	0.0
2	4000	1.0

分析の概要

PERCENT_RANK()関数を使用して、各エリア内の売上高に基づくパーセンタイルランクを計算します。この方法により、同じエリア内の他の売上と比較して、各売上がどの程度の位置にあるかを示します。この分析は、地域別のパフォーマンスを評価し、特定の地域における販売戦略を調整するのに役立ちます。

キャンペーン効果の前後比較

テーブル構造: CampaignSales (date, campaign_flag, sales_amount)
サンプルデータ:

date	campaign_flag	sales_amount
2023-01-01	0	1000
2023-02-01	1	1500
2023-03-01	0	1100

サンプルSQL:

SELECT
  campaign_flag,
  SUM(sales_amount) AS TotalSales
FROM CampaignSales
GROUP BY campaign_flag;

SELECT

campaign_flag,

SUM(sales_amount) AS TotalSales

FROM CampaignSales

GROUP BY campaign_flag;

出力結果:

campaign_flag	TotalSales
0	2100
1	1500

分析の概要

このSQLでは、SUM()関数を使ってキャンペーン期間中と非キャンペーン期間の売上合計を比較します。キャンペーンの効果を量的に評価することで、将来のマーケティング戦略の改善に役立つ洞察を提供します。

高価値顧客の特定

テーブル構造: CustomerSales (customer_id, sales_amount)
サンプルデータ:

customer_id	sales_amount
1	500
2	700
3	400
4	600

サンプルSQL:

SELECT
  customer_id,
  sales_amount,
  NTILE(4) OVER (ORDER BY sales_amount DESC) AS ValueGroup
FROM CustomerSales;

SELECT

customer_id,

sales_amount,

NTILE(4) OVER (ORDER BY sales_amount DESC) AS ValueGroup

FROM CustomerSales;

出力結果:

customer_id	sales_amount	ValueGroup
2	700	1
4	600	2
1	500	3
3	400	4

分析の概要

NTILE(4)関数を使用して、顧客を売上高に基づき4つのグループに分け、高価値顧客を特定します。この分析は、顧客セグメンテーションやターゲットマーケティング戦略の策定に役立ちます。

遅延支払いの分析

テーブル構造: Payments (invoice_id, due_date, payment_date)
サンプルデータ:

invoice_id	due_date	payment_date
1	2023-01-10	2023-01-15
2	2023-02-05	2023-02-04
3	2023-02-20	2023-03-01
4	2023-03-15	2023-03-14

サンプルSQL:

SELECT 
  invoice_id,
  due_date,
  payment_date,
  CASE 
    WHEN payment_date > due_date THEN DATEDIFF(day, due_date, payment_date) 
    ELSE 0 
  END AS DaysLate
FROM Payments;

SELECT

invoice_id,

due_date,

payment_date,

CASE

WHEN payment_date > due_date THEN DATEDIFF(day, due_date, payment_date)

ELSE 0

END AS DaysLate

FROM Payments;

出力結果:

invoice_id	due_date	payment_date	DaysLate
1	2023-01-10	2023-01-15	5
2	2023-02-05	2023-02-04	0
3	2023-02-20	2023-03-01	9
4	2023-03-15	2023-03-14	0

分析の概要

この分析では、DATEDIFF()関数を使用して、請求書の支払いが期限を過ぎてから実際に支払われるまでの日数（遅延日数）を計算します。支払いが期限内に行われた場合は遅延日数を0としています。この分析により、遅延支払いの傾向を把握し、遅延が頻繁に発生している顧客を特定することができます。これにより、支払い条件の見直しやリマインダーシステムの導入など、効果的な対策を講じることが可能になります。

顧客別平均取引額の分析

テーブル構造: Transactions (customer_id, transaction_amount)
サンプルデータ:

customer_id	transaction_amount
1	120
1	150
2	200
2	240
3	180
3	220

サンプルSQL:

SELECT 
  customer_id,
  AVG(transaction_amount) AS AverageAmount
FROM Transactions
GROUP BY customer_id;

SELECT

customer_id,

AVG(transaction_amount) AS AverageAmount

FROM Transactions

GROUP BY customer_id;

出力結果:

customer_id	AverageAmount
1	135
2	220
3	200

分析の概要

このクエリでは、AVG()関数を用いて、各顧客の平均取引額を計算します。顧客ごとに取引額の平均を求めることで、顧客の価値をより深く理解し、マーケティングやセールス戦略を最適化するための洞察を得ることができます。また、高い平均取引額を持つ顧客群に焦点を当てることで、ビジネスの利益向上に貢献する可能性があります。

まとめ

SQL Serverの分析関数を駆使することで、ビジネスデータの集計を効率的に行うことができます。この記事で紹介した10の具体例を通じて、データ分析のスキルを次のレベルへと引き上げることが可能です。売上分析から顧客行動分析まで、分析関数はビジネス戦略をより効果的にするための強力なツールです。今日からでもこれらの関数を活用し、データを通じて新たな価値を発見しましょう。