时间戳

首页 > 解决方案 > 要知道哪个时间段有很多记录

google-bigquery - 要知道哪个时间段有很多记录

问题描述

我有如下表。
时间段的条件是 0 到 6、7 到 12、13 到 18
是否有任何单个查询可以获取此输出？

销售材料销售时间

三星 2
三星 4
三星 3
诺基亚 5
诺基亚 13
诺基亚 10
苹果 12
苹果 7
苹果 15

预期输出为

时间段销售材料    
0 到 6 SAMSUNG

标签： google-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT start, finish,
  STRING_AGG(
    CONCAT(sellingMaterial, ':', CAST(volume AS STRING)) 
    ORDER BY volume DESC, sellingMaterial
  ) sellingMaterial
FROM (
  SELECT start, finish, sellingMaterial, COUNT(1) volume
  FROM `project.dataset.conditions` c
  JOIN `project.dataset.table` t
  ON sellingTime BETWEEN start AND finish
  GROUP BY start, finish, sellingMaterial
)
GROUP BY start, finish

您可以使用您问题中的示例数据进行测试，使用上面的示例数据，如下例所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'SAMSUNG' sellingMaterial, 2 sellingTime UNION ALL
  SELECT 'SAMSUNG', 4 UNION ALL
  SELECT 'SAMSUNG', 3 UNION ALL
  SELECT 'NOKIA', 5 UNION ALL
  SELECT 'NOKIA', 13 UNION ALL
  SELECT 'NOKIA', 10 UNION ALL
  SELECT 'APPLE', 12 UNION ALL
  SELECT 'APPLE', 7 UNION ALL
  SELECT 'APPLE', 15 
), `project.dataset.conditions` AS (
  SELECT 0 start, 6 finish UNION ALL
  SELECT 7, 12 UNION ALL
  SELECT 13, 18 
)
SELECT start, finish,
  STRING_AGG(
    CONCAT(sellingMaterial, ':', CAST(volume AS STRING)) 
    ORDER BY volume DESC, sellingMaterial
  ) sellingMaterial
FROM (
  SELECT start, finish, sellingMaterial, COUNT(1) volume
  FROM `project.dataset.conditions` c
  JOIN `project.dataset.table` t
  ON sellingTime BETWEEN start AND finish
  GROUP BY start, finish, sellingMaterial
)
GROUP BY start, finish
-- ORDER BY start

结果 

Row start   finish  sellingMaterial  
1   0       6       SAMSUNG:3,NOKIA:1    
2   7       12      APPLE:2,NOKIA:1  
3   13      18      APPLE:1,NOKIA:1

我要哪个时间记录最多[卖素材] 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'SAMSUNG' sellingMaterial, 2 sellingTime UNION ALL
  SELECT 'SAMSUNG', 4 UNION ALL
  SELECT 'SAMSUNG', 3 UNION ALL
  SELECT 'NOKIA', 5 UNION ALL
  SELECT 'NOKIA', 13 UNION ALL
  SELECT 'NOKIA', 10 UNION ALL
  SELECT 'APPLE', 12 UNION ALL
  SELECT 'APPLE', 7 UNION ALL
  SELECT 'APPLE', 15 
), `project.dataset.conditions` AS (
  SELECT 0 start, 6 finish UNION ALL
  SELECT 7, 12 UNION ALL
  SELECT 13, 18 
)
SELECT start, finish, sellingMaterial
FROM `project.dataset.conditions` c
JOIN `project.dataset.table` t
ON sellingTime BETWEEN start AND finish
GROUP BY start, finish, sellingMaterial
ORDER BY COUNT(1) DESC
LIMIT 1

结果 

Row start   finish  sellingMaterial  
1   0       6       SAMSUNG

推荐阅读