Stack Overflow データ

BigQuery を使用して一般公開データセットをクエリする方法

BigQuery は、フルマネージドのデータ ウェアハウスおよび分析プラットフォームです。SQL クエリを使用して分析できる一般公開データセットが用意されています。BigQuery の一般公開データセットにアクセスするには、ウェブ UIコマンドライン ツールを使用します。また、Java.NETPython など、さまざまなクライアント ライブラリを使用して BigQuery REST API を呼び出してアクセスすることもできます。

現在、BigQuery の一般公開データセットは US マルチリージョン ロケーションに格納されています。一般公開データセットをクエリする場合は、コマンドラインで --location=US フラグを指定するか、BigQuery ウェブ UI で処理ロケーションとして US を選択するか、または API でジョブリソースjobReference セクションの location プロパティを指定します。一般公開データセットは米国内に格納されているため、一般公開データのクエリ結果を別のリージョンにあるテーブルに書き込むことはできず、一般公開データセットのテーブルを別のリージョンにあるテーブルと結合することもできません。

BigQuery の一般公開データセットの使用を開始するには、プロジェクトを作成または選択します。毎月、最初の 1 テラバイトのデータの処理については課金されないため、課金を有効にせずに一般公開データセットのクエリを開始できます。無料枠を超える可能性がある場合は、課金を有効にする必要があります。

  1. Google アカウントにログインします。

    Google アカウントをまだお持ちでない場合は、新しいアカウントを登録します。

  2. GCP プロジェクトを選択または作成します。

    [リソースの管理] ページに移動

  3. プロジェクトに対して課金が有効になっていることを確認します。

    課金を有効にする方法について

  4. 新しいプロジェクトでは、BigQuery が自動的に有効になります。既存のプロジェクトで BigQuery を有効にする手順は以下のとおりです。 BigQuery API を有効にします。

    APIを有効にする

データセットの概要

Stack Overflow は、プログラマーが学習して、知識を共有し、キャリアを向上させるための最大のオンライン コミュニティです。四半期ごとに更新されるこの BigQuery データセットには、投稿、投票、タグ、バッジなどの Stack Overflow コンテンツのアーカイブが含まれています。このデータセットは Internet Archive 上の Stack Overflow コンテンツを反映するように更新されており、Stack Exchange Data Explorer から利用することもできます。

BigQuery コンソールで、このデータを探索してみましょう。

Stack Overflow データセットに移動

サンプルクエリ

BigQuery を使用してこのデータで実行できる SQL クエリの例をいくつか示します。

このサンプルでは、BigQuery の標準 SQL サポートを使用します。#standardSQL タグを使用して、標準 SQL を使用することを BigQuery に認識させる必要があります。#standardSQL 接頭辞の詳細については、クエリ接頭辞の設定をご覧ください。

回答された質問の割合は年毎にどうなっているか?

この短いクエリは、2009 年から 2015 年までに Stack Overflow に投稿された質問の件数と 1 年間に回答された割合を調査します。投稿された質問の総数は毎年増加していますが、回答された質問の割合は毎年減少しています。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  EXTRACT(YEAR FROM creation_date) AS Year,
  COUNT(*) AS Number_of_Questions,
  ROUND(100 * SUM(IF(answer_count > 0, 1, 0)) / COUNT(*), 1) AS Percent_Questions_with_Answers
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY
  Year
HAVING
  Year > 2008 AND Year < 2016
ORDER BY
  Year

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  EXTRACT(YEAR FROM creation_date) AS Year,
  COUNT(*) AS Number_of_Questions,
  ROUND(100 * SUM(IF(answer_count > 0, 1, 0)) / COUNT(*), 1) AS Percent_Questions_with_Answers
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY
  Year
HAVING
  Year > 2008 AND Year < 2016
ORDER BY
  Year
'

結果を以下に示します。

+------+---------------------+--------------------------------+
| Year | Number_of_Questions | Percent_Questions_with_Answers |
+------+---------------------+--------------------------------+
| 2009 |              345864 |                           99.5 |
| 2010 |              702964 |                           98.1 |
| 2011 |             1213146 |                           96.3 |
| 2012 |             1664204 |                           93.6 |
| 2013 |             2076336 |                           90.9 |
| 2014 |             2179015 |                           87.6 |
| 2015 |             2388670 |                           79.5 |
+------+---------------------+--------------------------------+

StackOverflow 上のさまざまな期間のユーザーの評価とバッジ数はどうなっているか?

このクエリは、Stack Overflow ユーザーをプラットフォーム上に存在した年数別にさまざまな集団に分類し、各集団の平均評価とバッジ数を計算します。StackOverflow 上の存在期間が長いユーザーの方が、平均評判が高く、バッジ数が多いことは想像がつきます。加えて、多くのユーザーが StackOverflow 上で 2 年後に複数のバッジを獲得し始めることが確認されたのは興味深いことです。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT User_Tenure,
       COUNT(1) AS Num_Users,
       ROUND(AVG(reputation)) AS Avg_Reputation,
       ROUND(AVG(num_badges)) AS Avg_Num_Badges
FROM (
  SELECT users.id AS user,
         ROUND(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), ANY_VALUE(users.creation_date), DAY)/365) AS user_tenure,
         ANY_VALUE(users.reputation) AS reputation,
         SUM(IF(badges.user_id IS NULL, 0, 1)) AS num_badges
  FROM `bigquery-public-data.stackoverflow.users` users
  LEFT JOIN `bigquery-public-data.stackoverflow.badges` badges
  ON users.id = badges.user_id
  GROUP BY user
)
GROUP BY User_Tenure
ORDER BY User_Tenure

コマンドライン

bq query --use_legacy_sql=false '
SELECT User_Tenure,
       COUNT(1) AS Num_Users,
       ROUND(AVG(reputation)) AS Avg_Reputation,
       ROUND(AVG(num_badges)) AS Avg_Num_Badges
FROM (
  SELECT users.id AS user,
         ROUND(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), ANY_VALUE(users.creation_date), DAY)/365) AS user_tenure,
         ANY_VALUE(users.reputation) AS reputation,
         SUM(IF(badges.user_id IS NULL, 0, 1)) AS num_badges
  FROM `bigquery-public-data.stackoverflow.users` users
  LEFT JOIN `bigquery-public-data.stackoverflow.badges` badges
  ON users.id = badges.user_id
  GROUP BY user
)
GROUP BY User_Tenure
ORDER BY User_Tenure
'

結果を以下に示します。

+-------------+-----------+----------------+----------------+
| User_Tenure | Num_Users | Avg_Reputation | Avg_Num_Badges |
+-------------+-----------+----------------+----------------+
|         0.0 |     46218 |            2.0 |            1.0 |
|         1.0 |   1401011 |            8.0 |            1.0 |
|         2.0 |   1198233 |           20.0 |            1.0 |
|         3.0 |   1175131 |           37.0 |            2.0 |
|         4.0 |    940001 |           82.0 |            3.0 |
|         5.0 |    471452 |          234.0 |            7.0 |
|         6.0 |    272851 |          456.0 |           10.0 |
|         7.0 |    129695 |         1017.0 |           17.0 |
|         8.0 |     42666 |         4031.0 |           45.0 |
+-------------+-----------+----------------+----------------+

獲得が「容易な」ゴールドバッジ 10 個は何か?

このクエリは、獲得しやすいゴールドバッジを調査し、初めてのゴールドバッジとして獲得したユーザー数によってランク付けされた上位 10 個のゴールドバッジを特定します。また、これらのゴールドバッジを取得するのにかかった平均日数を抽出します。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT badge_name AS First_Gold_Badge,
       COUNT(1) AS Num_Users,
       ROUND(AVG(tenure_in_days)) AS Avg_Num_Days
FROM
(
  SELECT
    badges.user_id AS user_id,
    badges.name AS badge_name,
    TIMESTAMP_DIFF(badges.date, users.creation_date, DAY) AS tenure_in_days,
    ROW_NUMBER() OVER (PARTITION BY badges.user_id
                       ORDER BY badges.date) AS row_number
  FROM
    `bigquery-public-data.stackoverflow.badges` badges
  JOIN
    `bigquery-public-data.stackoverflow.users` users
  ON badges.user_id = users.id
  WHERE badges.class = 1
)
WHERE row_number = 1
GROUP BY First_Gold_Badge
ORDER BY Num_Users DESC
LIMIT 10

コマンドライン

bq query --use_legacy_sql=false '
SELECT badge_name AS First_Gold_Badge,
       COUNT(1) AS Num_Users,
       ROUND(AVG(tenure_in_days)) AS Avg_Num_Days
FROM
(
  SELECT
    badges.user_id AS user_id,
    badges.name AS badge_name,
    TIMESTAMP_DIFF(badges.date, users.creation_date, DAY) AS tenure_in_days,
    ROW_NUMBER() OVER (PARTITION BY badges.user_id
                       ORDER BY badges.date) AS row_number
  FROM
    `bigquery-public-data.stackoverflow.badges` badges
  JOIN
    `bigquery-public-data.stackoverflow.users` users
  ON badges.user_id = users.id
  WHERE badges.class = 1
)
WHERE row_number = 1
GROUP BY First_Gold_Badge
ORDER BY Num_Users DESC
LIMIT 10
'

結果を以下に示します。

+------------------+-----------+--------------+
| First_Gold_Badge | Num_Users | Avg_Num_Days |
+------------------+-----------+--------------+
| Famous Question  |    144416 |       1094.0 |
| Fanatic          |     12992 |        579.0 |
| Unsung Hero      |     11392 |        555.0 |
| Great Answer     |      8722 |       1263.0 |
| Electorate       |      4722 |        769.0 |
| Populist         |      4631 |       1147.0 |
| Steward          |       743 |        924.0 |
| Great Question   |       537 |        684.0 |
| Copy Editor      |       232 |        533.0 |
| Marshal          |       136 |        553.0 |
+------------------+-----------+--------------+

1 時間以内に最も多くの質問が回答された曜日はどれか?

このクエリでは、迅速な回答を得たい場合に、どの曜日に質問するのが一番適切かを調査します。このクエリは、1 から 7 までの整数としての曜日(1 = 日曜日、2 = 月曜日など)と 2016 年の各日の質問の件数を返します。日ごとに、提出から 1 時間以内に回答された質問の件数とそれに対応する割合も照会します。質問と回答の件数は週の半ば(火曜日、水曜日、および木曜日)が最も多く、1 時間以内に回答された質問の割合は平日よりも土日の方が低いものの、差はほとんどありません。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  Day_of_Week,
  COUNT(1) AS Num_Questions,
  SUM(answered_in_1h) AS Num_Answered_in_1H,
  ROUND(100 * SUM(answered_in_1h) / COUNT(1),1) AS Percent_Answered_in_1H
FROM
(
  SELECT
    q.id AS question_id,
    EXTRACT(DAYOFWEEK FROM q.creation_date) AS day_of_week,
    MAX(IF(a.parent_id IS NOT NULL AND
           (UNIX_SECONDS(a.creation_date)-UNIX_SECONDS(q.creation_date))/(60*60) <= 1, 1, 0)) AS answered_in_1h
  FROM
    `bigquery-public-data.stackoverflow.posts_questions` q
  LEFT JOIN
    `bigquery-public-data.stackoverflow.posts_answers` a
  ON q.id = a.parent_id
  WHERE EXTRACT(YEAR FROM a.creation_date) = 2016
    AND EXTRACT(YEAR FROM q.creation_date) = 2016
  GROUP BY question_id, day_of_week
)
GROUP BY
  Day_of_Week
ORDER BY
  Day_of_Week

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  Day_of_Week,
  COUNT(1) AS Num_Questions,
  SUM(answered_in_1h) AS Num_Answered_in_1H,
  ROUND(100 * SUM(answered_in_1h) / COUNT(1),1) AS Percent_Answered_in_1H
FROM
(
  SELECT
    q.id AS question_id,
    EXTRACT(DAYOFWEEK FROM q.creation_date) AS day_of_week,
    MAX(IF(a.parent_id IS NOT NULL AND
           (UNIX_SECONDS(a.creation_date)-UNIX_SECONDS(q.creation_date))/(60*60) <= 1, 1, 0)) AS answered_in_1h
  FROM
    `bigquery-public-data.stackoverflow.posts_questions` q
  LEFT JOIN
    `bigquery-public-data.stackoverflow.posts_answers` a
  ON q.id = a.parent_id
  WHERE EXTRACT(YEAR FROM a.creation_date) = 2016
    AND EXTRACT(YEAR FROM q.creation_date) = 2016
  GROUP BY question_id, day_of_week
)
GROUP BY
  Day_of_Week
ORDER BY
  Day_of_Week
'

結果を以下に示します。

+-------------+---------------+--------------------+------------------------+
| Day_of_Week | Num_Questions | Num_Answered_in_1H | Percent_Answered_in_1H |
+-------------+---------------+--------------------+------------------------+
|           1 |         76315 |              44978 |                   58.9 |
|           2 |        132876 |              80031 |                   60.2 |
|           3 |        145685 |              88401 |                   60.7 |
|           4 |        149534 |              90392 |                   60.4 |
|           5 |        146850 |              88898 |                   60.5 |
|           6 |        133790 |              81357 |                   60.8 |
|           7 |         78809 |              46306 |                   58.8 |
+-------------+---------------+--------------------+------------------------+

データについて

データセット ソース: https://archive.org/download/stackexchange

カテゴリ: 雑学、研究

使用: cc-by-sa 3.0

更新頻度: 四半期ごと

BigQuery で表示: Stack Overflow データセットに移動

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。