Redshift

Snippets

プレフィクスを除去する

REPLACE('hoge_xxx', 'hoge_', '') でもよいがプレフィクスであれば以下の方が速い。

SELECT SUBSTRING('hoge_xxx', LENGTH('hoge_') + 1)

グループごとの値の登場数ランキングを集計する

各人の好きな果物テーブルからクラスごとの好きな果物ランキングを集計する。登場数が同じなら果物名で順位を付ける場合は ORDER BY COUNT(*) DESC, fruit とする。

WITH sample_table AS (
  SELECT '1年1組' AS class_, 'りんご' AS fruit UNION ALL
  SELECT '1年1組' AS class_, 'りんご' AS fruit UNION ALL
  SELECT '1年1組' AS class_, 'バナナ' AS fruit UNION ALL
  SELECT '1年1組' AS class_, 'バナナ' AS fruit UNION ALL
  SELECT '1年1組' AS class_, 'バナナ' AS fruit UNION ALL
  SELECT '1年2組' AS class_, 'バナナ' AS fruit UNION ALL
  SELECT '1年2組' AS class_, 'みかん' AS fruit UNION ALL
  SELECT '1年2組' AS class_, 'みかん' AS fruit UNION ALL
  SELECT '1年2組' AS class_, 'みかん' AS fruit UNION ALL
  SELECT '1年2組' AS class_, 'みかん' AS fruit UNION ALL
  SELECT '1年2組' AS class_, 'キウイ' AS fruit
),
tmp_table_0 AS (
  SELECT
    class_, fruit, COUNT(*) AS n_student,
    RANK() OVER (PARTITION BY class_ ORDER BY COUNT(*) DESC, fruit) AS rank
  FROM sample_table GROUP BY class_, fruit
)
SELECT
  class_, SUM(n_student) AS n_student,
  MAX(CASE WHEN rank = 1 THEN fruit || '(' || n_student::VARCHAR || ')' ELSE NULL END) AS fruit_1,
  MAX(CASE WHEN rank = 2 THEN fruit || '(' || n_student::VARCHAR || ')' ELSE NULL END) AS fruit_2,
  MAX(CASE WHEN rank = 3 THEN fruit || '(' || n_student::VARCHAR || ')' ELSE NULL END) AS fruit_3
FROM tmp_table_0
GROUP BY class_
ORDER BY class_

class_, n_student, fruit_1,  fruit_2,  fruit_3
1年1組, 5,         バナナ(3), りんご(2), 
1年2組, 6,         みかん(4), キウイ(1), バナナ(1)