ジュニアデータアナリスト面接質問：SQL・Excel・BI

# VLOOKUPの例
=VLOOKUP(A2, Products!A:C, 3, FALSE)

# SUMIFSの例（地域="East"、製品="Widget"の売上を合計）
=SUMIFS(Sales!C:C, Sales!A:A, "East", Sales!B:B, "Widget")

# INDEX/MATCHの例（VLOOKUPよりも柔軟）
=INDEX(Prices!C:C, MATCH(A2, Prices!A:A, 0))

# IFを使用した条件付き書式
=IF(B2>1000, "High", IF(B2>500, "Medium", "Low"))

# VLOOKUP - A列の値を検索し、C列から返す
=VLOOKUP(A2, A:C, 3, FALSE)

# INDEX/MATCH - 同等だがより柔軟
=INDEX(C:C, MATCH(A2, A:A, 0))

# INDEX/MATCHの利点：左側を検索できる
=INDEX(A:A, MATCH(C2, C:C, 0))  # VLOOKUPではこれはできない

# ピボットテーブルの構造：
行: 製品カテゴリ
列: 四半期
値: 売上の合計
フィルター: 地域

# ピボットテーブルの計算フィールド
利益率 = (収益 - コスト) / 収益

# 日付のグループ化
日付を右クリック → グループ → 月/四半期/年を選択

# 1000より大きいセルを強調表示
範囲を選択 → 条件付き書式 → セルの強調表示ルール → 指定の値より大きい

# カラースケール（グラデーション）
範囲を選択 → 条件付き書式 → カラースケール

# データバー
範囲を選択 → 条件付き書式 → データバー

# カスタム数式
=AND($B2>1000, $C2="Active")

# 重複の削除
データタブ → 重複の削除 → 列を選択

# 条件付き書式で重複を見つける
範囲を選択 → 条件付き書式 → セルの強調表示ルール → 重複する値

# 欠損データの処理
# オプション1：フィルターして削除
列をフィルター → (空白)のチェックを外す → 表示されている行を削除

# オプション2：平均で埋める
=IF(ISBLANK(A2), AVERAGE(A:A), A2)

# オプション3：下にコピー
範囲を選択 → Ctrl+D (Windows) または Cmd+D (Mac)

# 空白を検索して置換
Ctrl+H → 検索する文字列: (空白のまま) → 置換後の文字列: 0 または N/A

-- すべての列を選択
SELECT * FROM employees;

-- 特定の列を選択
SELECT first_name, last_name, salary
FROM employees;

-- エイリアスで選択
SELECT 
    first_name AS "First Name",
    last_name AS "Last Name",
    salary AS "Annual Salary"
FROM employees;

-- 重複しない値を選択
SELECT DISTINCT department
FROM employees;

-- 結果を制限
SELECT * FROM employees
LIMIT 10;

-- 単純な条件
SELECT * FROM employees
WHERE salary > 50000;

-- ANDを使用した複数の条件
SELECT * FROM employees
WHERE department = 'Sales' AND salary > 60000;

-- ORを使用した複数の条件
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

-- IN演算子
SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing', 'IT');

-- BETWEEN演算子
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 80000;

-- LIKE演算子（パターンマッチング）
SELECT * FROM employees
WHERE first_name LIKE 'J%';  -- Jで始まる

-- IS NULL
SELECT * FROM employees
WHERE manager_id IS NULL;

-- NOT演算子
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Finance');

-- INNER JOIN
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- LEFT JOIN (部署のない従業員も含むすべての従業員)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 複数のJOIN
SELECT 
    e.first_name,
    d.department_name,
    l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id;

-- 自己結合（従業員とそのマネージャー）
SELECT 
    e.first_name AS employee,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- 部署ごとの従業員数をカウント
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 部署ごとの平均給与
SELECT 
    department,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- HAVING句（グループをフィルタリング）
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- 複数のグループ化列
SELECT 
    department,
    job_title,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_salary DESC;

-- 一般的な集計関数
-- COUNT, SUM, AVG, MIN, MAX, COUNT(DISTINCT)

-- WHERE - グループ化する前にフィルタリング
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 40000  -- 個々の行をフィルタリング
GROUP BY department;

-- HAVING - グループ化後にフィルタリング
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;  -- グループをフィルタリング

-- 両方を一緒に使用
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'  -- 最初に行をフィルタリング
GROUP BY department
HAVING COUNT(*) > 5;  -- 次にグループをフィルタリング

-- これは間違っています：
-- SELECT department FROM employees
-- WHERE COUNT(*) > 5;  -- エラー：WHEREで集計関数を使用できない

import numpy as np

data = [1, 2, 2, 3, 4, 5, 100]

mean = np.mean(data)  # 16.71 (外れ値100の影響を受ける)
median = np.median(data)  # 3 (外れ値の影響を受けない)
# mode = 2 (最も頻繁)

print(f"平均: {mean}")
print(f"中央値: {median}")

import numpy as np

data = np.array([10, 12, 13, 12, 11, 14, 13, 15, 100, 12])

# IQR法
Q1 = np.percentile(data, 25)
Q3 = np.percentile(data, 75)
IQR = Q3 - Q1

lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

outliers = data[(data < lower_bound) | (data > upper_bound)]
print(f"外れ値: {outliers}")  # [100]

# Zスコア法
mean = np.mean(data)
std = np.std(data)
z_scores = np.abs((data - mean) / std)
outliers_z = data[z_scores > 3]
print(f"外れ値 (Zスコア): {outliers_z}")

-- SQLで相関関係を計算 (簡略化)
SELECT 
    CORR(sales, temperature) AS correlation
FROM daily_data;

-- 正の相関：両方とも一緒に増加
-- 負の相関：一方が増加すると、もう一方が減少
-- 相関なし：関係なし

# 数式
変化率 = ((新しい値 - 古い値) / 古い値) × 100

# Excelの数式
=(B2-A2)/A2*100

# 例:
古い値: 100
新しい値: 120
変化: (120-100)/100 = 0.20 = 20% 増加

# 前年比成長率
=(Sales_2023 - Sales_2022) / Sales_2022 * 100

-- SQLの変化率
SELECT 
    year,
    revenue,
    LAG(revenue) OVER (ORDER BY year) AS prev_year_revenue,
    ((revenue - LAG(revenue) OVER (ORDER BY year)) / 
     LAG(revenue) OVER (ORDER BY year) * 100) AS pct_change
FROM annual_sales;