Dados do Stack Overflow

Como consultar conjuntos de dados públicos usando o BigQuery

O BigQuery é um serviço de armazenamento de dados totalmente gerenciado e uma plataforma analítica. Os conjuntos de dados públicos estão disponíveis para serem analisados com consultas SQL. Para acessar conjuntos de dados públicos do BigQuery, basta usar a IU da Web, a ferramenta de linha de comando ou chamar a API REST do BigQuery usando várias bibliotecas de cliente, como Java, .NET ou Python.

Atualmente, os conjuntos de dados públicos do BigQuery são armazenados no local multirregional US. Ao consultar um conjunto de dados público, forneça a sinalização --location=US na linha de comando, escolha US como o local de processamento na IU da Web do BigQuery ou especifique a propriedade location na seção jobReference do recurso do job ao usar a API. Como os conjuntos de dados públicos são armazenados nos EUA, não é possível gravar resultados da consulta em uma tabela de outra região nem unir tabelas em conjuntos de dados públicos a tabelas de outra região.

Para começar a usar um conjunto de dados público do BigQuery, crie ou selecione um projeto. O primeiro terabyte de dados processados por mês é gratuito. Assim, você pode começar a consultar conjuntos de dados públicos sem ativar o faturamento. Para usar além do nível gratuito, você precisa ativar o faturamento.

  1. Faça login na sua Conta do Google.

    Se você ainda não tiver uma, inscreva-se.

  2. Selecione ou crie um projeto do GCP.

    Acessar a página Gerenciar recursos

  3. Verifique se o faturamento foi ativado para o projeto.

    Saiba como ativar o faturamento

  4. O BigQuery é ativado automaticamente em novos projetos. Para ativar o BigQuery em um projeto já existente, Ativar BigQuery API.

    Ativar API

Visão geral do conjunto de dados

Stack Overflow é a maior comunidade on-line para programadores aprenderem, compartilharem conhecimento e desenvolverem carreiras. O conjunto de dados do BigQuery inclui um arquivo de conteúdo do Stack Overflow que contém postagens, votos, tags e medalhas. Esse conjunto é atualizado para refletir o conteúdo do Stack Overflow no Internet Archive e também está disponível no Stack Exchange Data Explorer.

Comece a explorar esses dados no console do BigQuery:

Acessar o conjunto de dados do Stack Overflow

Consultas de exemplo

Aqui estão alguns exemplos de consultas SQL que você pode executar nesses dados no BigQuery.

Essas amostras usam a compatibilidade do BigQuery com o SQL padrão. Use a tag #standardSQL para informar ao BigQuery que você quer usar o SQL padrão. Para saber mais sobre o prefixo #standardSQL, consulte Como configurar o prefixo de uma consulta.

Qual é a porcentagem de perguntas respondidas ao longo dos anos?

Nessa pequena consulta, dois pontos são analisados: o número de perguntas postadas no Stack Overflow entre 2009 e 2015 e a porcentagem de perguntas respondidas anualmente. Podemos ver que, embora o número total de perguntas postadas esteja aumentando a cada ano, a porcentagem de perguntas respondidas vem caindo.

IU da Web

Abrir a consulta a seguir na IU da Web

#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

Linha de comando

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
'

Veja os resultados abaixo:

+------+---------------------+--------------------------------+
| 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 |
+------+---------------------+--------------------------------+

Qual é a reputação e a contagem de medalhas dos usuários nos diferentes coortes do Stack Overflow?

Nessa consulta, os usuários do Stack Overflow são separados em diferentes coortes pelo número de anos na plataforma. Para cada um desses coortes, são calculadas as médias de reputação e número de medalhas. Naturalmente, os usuários que estão há mais tempo nele apresentaram uma reputação melhor e um número maior de medalhas. Também é interessante ver que os usuários, em geral, só começam a ter mais medalhas após dois anos no Stack Overflow.

IU da Web

Abrir a consulta a seguir na IU da Web

#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

Linha de comando

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
'

Veja os resultados abaixo:

+-------------+-----------+----------------+----------------+
| 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 |
+-------------+-----------+----------------+----------------+

Quais são as 10 medalhas de ouro "mais fáceis" de conseguir?

Nessa consulta, você investiga quais são as medalhas de ouro mais fáceis de conseguir. Uma lista é gerada com as 10 principais medalhas de ouro, classificadas pelo número de usuários que as conquistaram como a primeira medalha desse tipo. Também analisamos quantos dias em média foram necessários para consegui-las.

IU da Web

Abrir a consulta a seguir na IU da Web

#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

Linha de comando

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
'

Veja os resultados abaixo:

+------------------+-----------+--------------+
| 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 |
+------------------+-----------+--------------+

Em qual dia da semana mais perguntas foram respondidas no intervalo de uma hora?

Nessa consulta, analisamos em qual dia da semana é melhor fazer perguntas para receber uma resposta mais rapidamente. Os dias da semana são exibidos como números inteiros de 1 a 7 (1 = domingo, 2 = segunda-feira e assim por diante) com o número de perguntas feitas em cada um deles em 2016. Também consultamos quantas perguntas foram respondidas até 1 hora após o envio e a porcentagem correspondente para cada dia. O volume de perguntas e respostas é maior no meio da semana (terça, quarta e quinta) e a porcentagem de perguntas respondidas dentro de 1 hora é menor no sábado e no domingo. Entretanto, essa porcentagem não é muito diferente se comparada com os dias úteis.

IU da Web

Abrir a consulta a seguir na IU da Web

#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

Linha de comando

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
'

Estes são os resultados:

+-------------+---------------+--------------------+------------------------+
| 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 |
+-------------+---------------+--------------------+------------------------+

Sobre os dados

Fonte do conjunto de dados: https://archive.org/download/stackexchange

Categoria: enciclopédia, pesquisa

Uso: cc-by-sa 3.0

Ver no BigQuery: acessar o conjunto de dados do Stack Overflow

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.