时间戳

首页 > 解决方案 > 有什么有效的方法来计算大数据表列中的二进制值？

python - 有什么有效的方法来计算大数据表列中的二进制值？

问题描述

我在谷歌云平台上使用大查询芝加哥犯罪数据。但是，我想计算每种犯罪类型的逮捕和未逮捕人数。在 pandas 中计算这一点很容易，但对我来说如何用大查询计算数据框中的二进制值并不直观。谁能给我一个可能的想法来计算这个数字？

数据

因为芝加哥犯罪数据很大，所以我无法在这里给出可重现的例子，但是从这里预览犯罪数据很容易：芝加哥犯罪数据

这是小预览：

犯罪数据预览

我的大问题：

SELECT
  primary_type,
  count(arrest),
  COUNTIF(year = 2015) AS arrests_2015,
  COUNTIF(year = 2016) AS arrests_2016
FROM
  `bigquery-public-data.chicago_crime.crime`
WHERE
  arrest = TRUE
  AND year IN (2001,
    2018)
  AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY
  primary_type,
  arrest

但是这个查询给了我空的输出，我不知道如何让它工作。

目标：

从芝加哥犯罪数据表中，我想提取每种主要类型的总逮捕和非逮捕数量，我想排除 OTHER OFFENSE 和所有非犯罪类型，直到 2018 年底。

如何更正我的大查询以获得预期的输出？任何有效的大查询脚本来获得预期的查询输出？任何想法？谢谢

标签： pythongoogle-bigquerybigdata

解决方案

下面应该工作 

#standardSQL
SELECT
  primary_type,
  COUNT(arrest) arrest_total,
  COUNTIF(year = 2015) AS arrests_2015,
  COUNTIF(year = 2016) AS arrests_2016
FROM `bigquery-public-data.chicago_crime.crime`
WHERE arrest = TRUE
AND year BETWEEN 2001 AND 2018
AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY primary_type, arrest

我认为您的问题在下面一行，您只选择了 2001 年和 2018 年，而不是两者之间的所有年份（至少包括 2015 年和 2016 年） 

AND year IN (2001, 2018)

所以，你应该使用以下一个 

AND year BETWEEN 2001 AND 2018

此外，如果您想包括非逮捕，您可以在下面使用 

#standardSQL
SELECT
  primary_type,
  arrest,
  COUNT(arrest) arrest_total,
  COUNTIF(year = 2015) AS arrests_2015,
  COUNTIF(year = 2016) AS arrests_2016
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year BETWEEN 2001 AND 2018
AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY primary_type, arrest

注意：我在这里删除WHERE arrest = TRUE并添加arrest到 SELECT 列表

而不是这几个调整 - 您的初始查询是非常正确的

如果你想有一个输出行，primary_type你可以在下面使用 

#standardSQL
SELECT
  primary_type,
  COUNTIF(arrest) arrests,
  COUNTIF(NOT arrest) non_arrests,
  COUNT(arrest) arrest_total,
  COUNTIF(year = 2015) AS arrests_2015,
  COUNTIF(year = 2016) AS arrests_2016
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year BETWEEN 2001 AND 2018
AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY primary_type

此外 - 如果您可以像下面这样“扩展”年份计数（例如 2015 年） 

COUNTIF(year = 2015 AND arrest) AS arrests_2015,
COUNTIF(year = 2015 AND NOT arrest) AS non_arrests_2015,

是否有任何程序化方法来计算每种犯罪类型从 2001 年到 2018 年的逮捕人数 

#standardSQL
SELECT
  primary_type,
  year,
  COUNTIF(arrest) arrests,
  COUNTIF(NOT arrest) non_arrests,
  COUNT(arrest) arrest_total
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year BETWEEN 2001 AND 2018
AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY primary_type, year

推荐阅读