我要列出所有销售人员的名单,并计算上周进行了多少次演示。
我正在使用模式分析,因此{{@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
挑战在于没有演示的人不会出现在此表中。
也许我可以做一个工会,只给他们一个演示任务,而不给他们任何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