Database Reference
In-Depth Information
COUNT(1) AS num
FROM [publicdata:samples.wikipedia]
GROUP EACH BY wp_namespace, contributor_id)
GROUP BY wp_namespace
ORDER BY wp_namespace
In addition to cardinality, this query also records the total counts broken
down by the same dimension. It is common to want both together, so it is
helpful to fetch them in a single query.
One nice capability of COUNT(DISTINCT field ) is that a single query can
determine the cardinality of multiple fields. With a little cleverness you can
modify your exact version to do the same.
SELECT wp_namespace,
SUM(IF(field = 'ID', 1, 0)) AS unique_id,
SUM(IF(field = 'ID', INTEGER(num), 0)) AS total_id,
SUM(IF(field = 'IP', 1, 0)) AS unique_ip,
SUM(IF(field = 'IP', INTEGER(num), 0)) AS total_ip,
FROM (
SELECT wp_namespace, field, COUNT(1) AS num
FROM (
SELECT wp_namespace,
'ID' AS field,
HASH(contributor_id) AS val
FROM [publicdata:samples.wikipedia]
WHERE contributor_id IS NOT NULL
), (
SELECT
wp_namespace,
'IP' AS field,
HASH(contributor_ip) AS val
FROM [publicdata:samples.wikipedia]
WHERE contributor_ip IS NOT NULL)
GROUP EACH BY wp_namespace, field, val)
GROUP BY wp_namespace
ORDER BY wp_namespace
Search WWH ::




Custom Search