sql - BigQuery SQL 如何在使用 LIMIT 时获取总计数

Question

如果我在 SQL 查询(使用 BigQuery)中使用 LIMIT 10，是否有办法同时返回总计数？

例如，存在 100 行。如何查询以返回前 10 行，同时向用户显示总共有多少行可用，而无需执行单独的 count(id) 聚合查询？

Answer 1

要添加到 Mikhail 的答案中，您可能需要执行此操作以查看分组查询中唯一值的计数。在以下示例中，R 有 10 个唯一值，但您只想查看前 4 个值以及唯一行的计数。我还添加了显示每组的计数和每行的总数。 (下面是标准SQL)

WITH YourTable AS (
  SELECT 1 AS r UNION ALL
  SELECT 3 AS r UNION ALL
  SELECT 4 AS r UNION ALL
  SELECT 4 AS r UNION ALL
  SELECT 4 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 1 AS r UNION ALL
  SELECT 2 AS r UNION ALL
  SELECT 3 AS r UNION ALL
  SELECT 4 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 1 AS r UNION ALL
  SELECT 2 AS r UNION ALL
  SELECT 3 AS r UNION ALL
  SELECT 4 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 1 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 1 AS r UNION ALL
  SELECT 2 AS r UNION ALL
  SELECT 3 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 1 AS r UNION ALL
  SELECT 2 AS r UNION ALL
  SELECT 3 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 1 AS r UNION ALL
  SELECT 2 AS r UNION ALL
  SELECT 3 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 1 AS r UNION ALL
  SELECT 2 AS r UNION ALL
  SELECT 3 AS r UNION ALL
  SELECT 4 AS r UNION ALL
  SELECT 5 AS r UNION ALL
  SELECT 6 AS r UNION ALL
  SELECT 7 AS r UNION ALL
  SELECT 8 AS r UNION ALL
  SELECT 9 AS r UNION ALL
  SELECT 10 AS r    
)
SELECT 
  r,
  SUM(1) OVER (ORDER BY r ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CountOfAllUniqueRows,
  COUNT(r) AS CountOfEachR,
  SUM(COUNT(R)) OVER (ORDER BY r ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CountOfAllRows
FROM YourTable
GROUP BY r
ORDER BY r
LIMIT 4

并给出结果:

r   CountOfAllUniqueRows    CountOfEachR    CountOfAllRows
1   10                      8               68
2   10                      6               68
3   10                      7               68
4   10                      6               68