【小ネタ】 ちょっとしたローカルでのSQL実行にDuckDBを使ったら便利だった

科目名,学期,学生数,合格者数,平均点,受講時間数
数学I,2025-前期,35,28,72.4,45
数学I,2025-後期,38,32,75.8,45
英語コミュニケーション,2025-前期,42,36,76.3,30
英語コミュニケーション,2025-後期,45,38,77.1,30
物理学基礎,2025-前期,25,15,58.6,60
物理学基礎,2025-後期,28,17,59.2,60
プログラミング入門,2025-前期,50,42,78.5,45
プログラミング入門,2025-後期,52,45,79.8,45
統計学,2025-前期,30,18,61.2,30
統計学,2025-後期,32,20,62.5,30
データベース概論,2025-前期,38,25,64.7,45
データベース概論,2025-後期,40,28,65.9,45
化学,2025-前期,22,12,55.3,60
化学,2025-後期,25,14,56.8,60

-- CSVファイルを読み込んでテーブルを作成
CREATE TABLE grades AS 
SELECT * FROM read_csv_auto('your-path/student_grades.csv');

-- データを確認
SELECT * FROM grades LIMIT 5;

-- 平均点_科目レベルの計算
WITH subject_level_avg AS (
    SELECT 
        科目名,
        学期,
        学生数,
        合格者数,
        平均点,
        受講時間数,
        AVG(平均点) OVER (PARTITION BY 科目名) AS 平均点_科目レベル
    FROM grades
    WHERE 学期 IN ('2025-前期', '2025-後期') -- 選択した学期をフィルタリング
)
SELECT * FROM subject_level_avg;

-- 不合格科目名の計算
WITH subject_level_avg AS (
    SELECT 
        科目名,
        学期,
        学生数,
        合格者数,
        平均点,
        受講時間数,
        AVG(平均点) OVER (PARTITION BY 科目名) AS 平均点_科目レベル
    FROM grades
    WHERE 学期 IN ('2025-前期', '2025-後期') -- 選択した学期をフィルタリング
),
failing_subjects AS (
    SELECT 
        科目名,
        学期,
        学生数,
        合格者数,
        平均点,
        受講時間数,
        平均点_科目レベル,
        CASE 
            WHEN 平均点_科目レベル < 60 THEN 科目名
            ELSE NULL
        END AS 不合格科目名
    FROM subject_level_avg
)
SELECT * FROM failing_subjects;

WITH subject_level_avg AS (
  SELECT 
	  科目名,
	  学期,
	  学生数,
	  合格者数,
	  平均点,
	  受講時間数,
	  AVG(平均点) OVER (PARTITION BY 科目名) AS 平均点_科目レベル
  FROM grades
  WHERE 学期 IN ('2025-前期', '2025-後期') -- 選択した学期をフィルタリング
),
failing_subjects AS (
  SELECT 
	  科目名,
	  学期,
	  学生数,
	  合格者数,
	  平均点,
	  受講時間数,
	  平均点_科目レベル,
	  CASE 
		  WHEN 平均点_科目レベル < 60 THEN 科目名
		  ELSE NULL
	  END AS 不合格科目名
  FROM subject_level_avg
)

SELECT 
    COUNT(DISTINCT 不合格科目名) AS 不合格科目数
FROM failing_subjects;