我想列出所有销售人员的名单,并统计他们上周做了多少演示。
我使用的是模式分析,所以{{@dummy_accounts}}和{{@active_sdrs}}只是子查询的快捷方式。第一个只是得到一个我们不想统计的帐户ID列表,第二个是当前正在工作的SDR。
挑战在于,我的过滤器会带走上周计划了0个演示的SDR。我仍然想把它们包括在我的结果集中,以表明他们还没有安排任何演示。
我该如何获得它,以便包括那些尚未安排演示的SDR?
SELECT full_name,
count(activity_id) AS total_demos
FROM by_task
WHERE task_type LIKE 'Demo'
AND created_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
AND account_id NOT IN
(SELECT account_id
FROM {{@dummy_accounts}})
AND full_name IN
(SELECT full_name
FROM {{@active_sdrs}})
GROUP BY 1
ORDER BY 2 DESC
--编辑--
以下是我希望数据集的样子。。。
full_name total_demos
John Doe 5
Billy Bob 3
Ray Mac 2
Jose McGuerro 0
Joe MacDonald 0
但是,以下是查询产生的结果。。。
full_name total_demos
John Doe 5
Billy Bob 3
Ray Mac 2
这里有几行显示了数据结构。。。
activity_id task date full_name account_id
324123ASe1Q Demo 2017-10-13 John Doe aa912
324123ASe3F Demo 2017-10-13 John Doe aa932
324123ASe8E Demo 2017-10-09 Billy Bob aa933
324123ASe9A Demo 2017-10-08 Ray Mac aa999
324123ASe9A Demo 2017-10-09 Ray Mac aa993
挑战在于,没有演示的人不会出现在这个表格中。
也许我可以做一个联合,只给他们一个Demo任务,不给他们任何activity_id,这样他们就不算在计数中了?不确定。
您似乎需要一个left join
。我想:
SELECT a.full_name, count(t.activity_id) AS total_demos
FROM {{@active_sdrs}} a left join
by_task t
on t.full_name = a.full_name and
t.task_type LIKE 'Demo'
t.created_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) and
t.account_id NOT IN (SELECT da.account_id FROM {{@dummy_accounts}} da)
GROUP BY 1
ORDER BY 2 DESC;
NOT IN
相当可疑。它通常写为:
SELECT a.full_name, count(t.activity_id) AS total_demos
FROM {{@active_sdrs}} a left join
by_task t
ON t.full_name = a.full_name and
t.task_type LIKE 'Demo' and
t.created_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) LEFT JOIN
{{@dummy_accounts}} da
ON t.account_id = da.account_id
WHERE da.account_id IS NULL
GROUP BY 1
ORDER BY 2 DESC
下面是BigQuery标准SQL
#standardSQL
WITH `by_task` AS (
SELECT '324123ASe1Q' activity_id, 'Demo' task_type, DATE '2017-10-13' created_date , 'John Doe' full_name, 'aa912' account_id UNION ALL
SELECT '324123ASe3F', 'Demo', DATE '2017-10-13', 'John Doe', 'aa932' UNION ALL
SELECT '324123ASe8E', 'Demo', DATE '2017-10-09', 'Billy Bob', 'aa933' UNION ALL
SELECT '324123ASe9A', 'Demo', DATE '2017-10-08', 'Ray Mac', 'aa999' UNION ALL
SELECT '324123ASe9A', 'Demo', DATE '2017-10-09', 'Ray Mac', 'aa993'
),
dummy_accounts AS (
SELECT '1111' account_id UNION ALL
SELECT '2222' UNION ALL
SELECT '3333'
),
active_sdrs AS (
SELECT 'John Doe' full_name UNION ALL
SELECT 'Billy Bob' UNION ALL
SELECT 'Ray Mac' UNION ALL
SELECT 'Jose McGuerro' UNION ALL
SELECT 'Joe MacDonald'
)
SELECT a.full_name, IFNULL(total_demos, 0) total_demos
FROM active_sdrs a
LEFT JOIN (
SELECT full_name, COUNT(activity_id) AS total_demos
FROM by_task
WHERE task_type LIKE 'Demo'
AND created_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
AND account_id NOT IN (SELECT account_id FROM dummy_accounts)
AND full_name IN (SELECT full_name FROM active_sdrs)
GROUP BY 1
) t
ON t.full_name = a.full_name
ORDER BY 2 DESC
如预期-输出为
full_name total_demos
------------- -----------
John Doe 2
Ray Mac 2
Billy Bob 1
Jose McGuerro 0
Joe MacDonald 0