【雑談】金曜日のネタ帳、年間集計をchatGPTとやり取りしたときのメモ

年末恒例のネタ帳投稿者ランキングを書きました。

【発表】2024年「金曜日のネタ帳」投稿者ランキング

今回過去の投稿者データを全部BigQueryに入れてみたところ、様々な角度の評価を入れることができました。
（今まではexcelでその年の投稿ランキング等・次点率・投稿者数などしか出していなかった）

これはBigQueryに入れてからChatGPTに作ってもらったクエリを使った記録です。

はじめにstring_field_0問題（未解決）

スプレッドシートにデータを入れてそこにBigQuery接続することで来年以降も使いまわしできるようにしようと思ったのですが、カラム名に「string_field_*」が出てきてしまう問題が発生。

これ（↑）

自動スキーマ検出の機能を使用する際におかしなデータがあったりするとこの状態になるということなんですが、元データを見てもよくわからず。
このスプレッドシートをcsv化して、テーブルにすると問題なく対応できるというのが謎。

結局よくわからなかったので、csvで対応。

なお、下記がcsvでアップした正しいデータです。

原因調査をGPT君に。

色々試してみたもののわからず…

各種集計をchatGPTにこんなふうに依頼した

上記csvファイルをchatGPTに上げて、プロンプトを投げていました。

そのやり取りは以下の通り。

10位以内のuserが2016年以降どのような投稿数とランキングだったかを調べたい

例えば下記のように書くとrankがすべて1になってしまいます。特定のユーザーにwhereした場合でも元のランキングを表示することができますか？

SELECT
  *,
  RANK() OVER (PARTITION BY year ORDER BY pts DESC) AS rank
FROM
  nanika-nageru.100_bq_challenge.neta-ranking
WHERE
  name ="user_name"
ORDER BY
  year
LIMIT
  1000

WITH ranked_data AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY year ORDER BY pts DESC) AS rank
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
)
SELECT
  *
FROM
  ranked_data
WHERE
  name = "user_name"
ORDER BY
  year
LIMIT
  1000;

再び下記について質問です。2016年から2024年のデータを取っているのですが、nameによっては特定の年しかデータがないこともあります。そこでない年のデータは値を0で返してほしいのですが可能ですか？

WITH ranked_data AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY year ORDER BY pts DESC) AS rank
  FROM
    nanika-nageru.100_bq_challenge.neta-ranking
)
SELECT
  *
FROM
  ranked_data
WHERE
  name = "JUNKOBAYASHIHoS"
ORDER BY
  year
LIMIT
  1000;

-- 年リストを生成
WITH years AS (
  SELECT year
  FROM UNNEST(GENERATE_ARRAY(2016, 2024)) 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
  y.year,
  r.name,
  IFNULL(r.pts, 0) AS pts,
  IFNULL(r.rank, 0) AS rank
FROM
  years y
LEFT JOIN
  ranked_data r
ON
  y.year = r.year AND r.name = "user_name"
ORDER BY
  y.year
LIMIT
  1000;

下記のような出力になります。これは地味に便利だった。

2024年自己ベスト賞（過去と比較して2024年のポイントがもっとも高かった人を知りたい）

同じデータを使って2024年に最高のptsを獲得した人を出すにはどんなクエリを書いたらいいですか？

SELECT
  name,
  pts,
  cnt,
  rank
FROM
  `nanika-nageru.100_bq_challenge.neta-ranking`
WHERE
  year = 2024
ORDER BY
  pts DESC
LIMIT
  1;

※上記は意図したものではないので再質問。

過去の自分と比較して2024年に最高のptsを出したというのを抽出するには？

WITH past_max_pts AS (
  -- 各ユーザーの過去（2016-2023年）の最高得点を計算
  SELECT
    name,
    MAX(pts) AS max_past_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year BETWEEN 2016 AND 2023
  GROUP BY
    name
),
current_2024_pts AS (
  -- 2024年の得点を取得
  SELECT
    name,
    pts AS current_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2024
)
-- 2024年の得点が過去の最高得点を上回る人を抽出
SELECT
  c.name,
  c.current_pts,
  p.max_past_pts
FROM
  current_2024_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;

こうなる。とてもいい。

（一応excelでいくつかデータ確認したのだけど、パソコンが出始めた時にパソコンの出力が正しいかを確認するために電卓で一度確認するおじさんムーブになっている気がするｗ）

2024年初投稿賞（2024年に初めてネタ帳投稿した人を知りたい）

これは先程のクエリの結果を見たのでやりやすかった。

2024年初投稿はさっきのクエリにmax_past_pts=nullを入れれば良い？

はい、その通りです！2024年が初投稿のユーザーは max_past_pts が NULL になるため、以下のように条件を追加すれば抽出できます。

WITH past_max_pts AS (
  -- 各ユーザーの過去（2016-2023年）の最高得点を計算
  SELECT
    name,
    MAX(pts) AS max_past_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year BETWEEN 2016 AND 2023
  GROUP BY
    name
),
current_2024_pts AS (
  -- 2024年の得点を取得
  SELECT
    name,
    pts AS current_pts
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2024
)
-- 2024年が初投稿のユーザーを抽出
SELECT
  c.name,
  c.current_pts
FROM
  current_2024_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;

こうなる。

カムバック賞（2022年より前に投稿してたけど去年は投稿なし。2024年に投稿した人を教えてほしい）

2022年以前に投稿があり2023年に投稿がなかったけど2024年に投稿した人の検出方法教えて

WITH 
previous_years AS (
  -- 2022年以前に投稿があったユーザーを取得
  SELECT
    DISTINCT name
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year <= 2022
),
no_post_2023 AS (
  -- 2023年に投稿がなかったユーザーを取得
  SELECT
    DISTINCT name
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2023
),
post_2024 AS (
  -- 2024年に投稿があったユーザーを取得
  SELECT
    DISTINCT name
  FROM
    `nanika-nageru.100_bq_challenge.neta-ranking`
  WHERE
    year = 2024
)
-- 条件を満たすユーザーを抽出
SELECT
  p.name
FROM
  previous_years p
LEFT JOIN
  no_post_2023 n
ON
  p.name = n.name
JOIN
  post_2024 a
ON
  p.name = a.name
WHERE
  n.name IS NULL -- 2023年に投稿がない
ORDER BY
  p.name;

そのクエリに2024年のptsも添えられるようにしたいです

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

こうなる。

まとめ

2024年の集計方法メモでした。
なお、このブログを更新するにあたりなぜかコメント機能が一部正常に動かずChatGPTに助けを求めていました。

引き続き積極的に頼っていこうと思います。本年もどうぞよろしくお願いいたします。

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

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

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

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

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

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

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

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

こちらのメールアドレスを送信先にしてください。