【俺得メモ】金曜日のネタ帳、BigQuery年間集計メモ

金曜日のネタ帳を去年BigQueryで管理するようになったのですが、毎年回しやすくするようにちょっと変えたので来年末の自分向けメモ。

毎年使えるクエリ

基本的には去年のを踏襲。
【雑談】金曜日のネタ帳、年間集計をchatGPTとやり取りしたときのメモ

やったことは下記。

• データが追加されたときのunionをコピペでできるようにする（前年までのデータと今年のデータを追加する）
• with句のtable名が2023とか2024の付いたものになっていて混乱しそうだったのでthisyearやpastyearにする

シンプルに来年の自分向けに使いやすくすることを目標にしました。

去年までのtableとunionする

2025_netaのcsvをアップロードしてそれとunion。既存tableに上書きする。

CREATE OR REPLACE TABLE `nanika-nageru.100_bq_challenge.neta-ranking`
AS (
  SELECT
    *
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  UNION ALL
  SELECT
    *
  FROM
    `nanika-nageru.100_bq_challenge.2025_neta`
)

アカウント別の年度集計

WITH years AS (
  SELECT year
  -- ここの年度を変更する
  FROM UNNEST(GENERATE_ARRAY(2016, 2025)) AS year
),
ranked_data AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY year ORDER BY pts DESC) AS rank
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
)
SELECT
  r.name,
  y.year,
  IFNULL(r.rank, 0) AS rank,
  IFNULL(r.pts, 0) AS pts,
  IFNULL(r.cnt, 0) AS cnts,
FROM
  years y
LEFT JOIN
  ranked_data r
ON
  y.year = r.year AND r.name = "user_name"
ORDER BY
  y.year
LIMIT
  1000;

こうなる。

自己ベスト賞

WITH past_max_pts AS (
  -- 各ユーザーの過去の最高得点を計算
  SELECT
    name,
    MAX(pts) AS max_past_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
      -- 一年前までのスコア
    year BETWEEN 2016 AND 2024
  GROUP BY
    name
),
current_pts AS (
  -- 今年の得点を取得
  SELECT
    name,
    pts AS current_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2025
)
-- 今年の得点が過去の最高得点を上回る人を抽出
SELECT
  c.name,
  c.current_pts,
  p.max_past_pts
FROM
  current_pts c
LEFT JOIN
  past_max_pts p
ON
  c.name = p.name
WHERE
  c.current_pts > IFNULL(p.max_past_pts, 0) -- 過去の得点がない場合は0と比較
ORDER BY
  c.current_pts DESC;

こうなる。

初投稿賞と区別するためにnullを除外してもいいような気もしてきた。

初投稿賞

WITH past_max_pts AS (
  -- 各ユーザーの過去の最高得点を計算
  SELECT
    name,
    MAX(pts) AS max_past_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
    -- 一年前までのスコア
  WHERE
    year BETWEEN 2016 AND 2024
  GROUP BY
    name
),
current_pts AS (
  -- 今年の得点を取得
  SELECT
    name,
    pts AS current_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2025
)
SELECT
  c.name,
  c.current_pts,
  p.max_past_pts
FROM
  current_pts c
LEFT JOIN
  past_max_pts p
ON
  c.name = p.name
WHERE
  p.max_past_pts IS NULL -- 過去の投稿がないユーザー
ORDER BY
  c.current_pts DESC;

こうなる。

カムバック賞

WITH 
previous_years AS (
  -- 2年前に投稿があったユーザーを取得
  SELECT
    DISTINCT name
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year <= 2023
),
no_post_pastyear AS (
  -- 2年前に投稿がなかったユーザーを取得
  SELECT
    DISTINCT name
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2024
),
post_one_year AS (
  -- 去年に投稿があったユーザーとその得点を取得
  SELECT
    name,
    SUM(pts) AS total_pts_thisyear
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2025
  GROUP BY
    name
)
-- 条件を満たすユーザーを抽出し、去年の得点を結合
SELECT
  p.name,
  a.total_pts_thisyear
FROM
  previous_years p
LEFT JOIN
  no_post_pastyear n
ON
  p.name = n.name
JOIN
  post_one_year a
ON
  p.name = a.name
WHERE
  n.name IS NULL -- 前年に投稿がない
ORDER BY
  a.total_pts_thisyear DESC;

こうなる。

まとめ

2025年の集計方法メモでした。

実はユーザー別の投稿動向もBigQuery内で完結するのではないかという気がしなくもないけどそれはまた何かの機会に。

良いお年を。

ブログへのサポートのお願い（アマゾンギフト）

いつも「バカに毛が生えたブログ」をご愛読いただきありがとうございます。

現在、このブログは皆様のおかげで無料・広告なし（※）で運営しております。
※AdSenseはセンシティブ判定されてしまうため、広告を掲載できません。

ブログの継続とさらなる充実のためご支援をお願いします。
支援は一度限りのショット支援として、¥150から可能です。

いただいた支援は、以下のような形で活用させていただきます：

• サーバー費用やサイトの維持
• 新しいコンテンツの作成
• モチベーション

ご希望の方はスパチャ読みをいたしますので、その旨をお知らせください。

ご支援はより良いブログを提供するための力になります。

こちらのメールアドレスを送信先にしてください。
↓タップするとコピーされます
メールアドレスをコピー