使用SQL从特定日期选择活动 [英] Select activity from a specific date using SQL

问题描述

我想查找特定日期在堆栈溢出问题和答案"数据集上被问到的问题数量. 在2018-11-11问了多少个问题?

I want to look up the number of questions are asked in a specific day on the Stack Overflow Question and answer dataset. How many questions were asked at 2018-11-11?

how = """SELECT
  EXTRACT(DAY FROM DATE '2018-11-11') AS Day,
  EXTRACT(MONTH FROM DATE '2018-11-11') AS Month,
  EXTRACT(YEAR FROM DATE '2018-11-11') 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
  Day
HAVING
  Day > 0 AND day < 12
ORDER BY
  Day;

    """


how = stackOverflow.query_to_pandas_safe(how)
how.head(12)

我使用的代码取回整个数据集中的所有问题，而不是在我选择的日期.如果我尝试使用@@过滤，则会收到错误消息

The code I use retrieves back all questions asked in the whole dataset Instead on the date I have selected. If I try to filter with @@ I get an error

推荐答案

查询不是这样吗?

SELECT COUNT(*) AS Number_of_Questions
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE DATE = DATE('2018-11-11');

我看到这是一个公共数据集.假设您是指创建日期，那么:

I see this is a public data set. Assuming you mean the creation date, then:

SELECT count(*)
FROM `bigquery-public-data.stackoverflow.posts_questions` pq
WHERE creation_date >= TIMESTAMP('2018-11-11') and
      creation_date < TIMESTAMP('2018-11-12') ;

此代码已经过测试，可以在我运行时正常工作.

This code is tested and works when I run it.

这篇关于使用SQL从特定日期选择活动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！