我要列出所有销售人员的名单,并计算上周进行了多少次演示。

我正在使用模式分析,因此{{@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

10-07 18:01