如何用average函数和where子句连接两个表?SQL

Question

下面有两个表，其中包含以下信息

project.analytics 
| proj_id | list_date  | state  
|   1     |   03/05/10 |   CA   
|   2     |   04/05/10 |   WA   
|   3     |   03/05/10 |   WA   
|   4     |   04/05/10 |   CA   
|   5     |   03/05/10 |   WA   
|   6     |   04/05/10 |   CA   
employees.analytics 
| employee_id  | proj_id  | worked_date
|   20         |   1      | 3/12/10    
|   30         |   1      | 3/11/10         
|   40         |   2      | 4/15/10     
|   50         |   3      | 3/16/10     
|   60         |   3      | 3/17/10     
|   70         |   4      | 4/18/10   

我可以写什么查询来确定在项目按月份和状态列出的前7天内参与项目的唯一员工的平均人数

期望输出：

| list_date | state  | # Unique Employees of projects first 7 day list 
|   March   |   CA   |   1
|   April   |   WA   |   2
|   July    |   WA   |   2
|   August  |   CA   |   1

我的尝试

select 
month(list_date),
state_name, 
count(*) as Projects,
from projects 
group by
month(list_date),
state_name;

我知道接下来的步骤是减去worked_date-list_date，如果值<7然后是第二个表中员工的平均计数，但我不确定该使用什么查询函数。

Answer 1

您可以使用带有DISTINCT的CASE来计算在list_date的前7天内工作的唯一员工。

一旦你有了每个项目的员工总数，那么你就可以计算出每个月的平均数；状态

SELECT 
MONTHNAME(list_date) as `ListMonth`,
state,
AVG(TotalUniqEmp7Days) AS `Average Unique Employees of projects first 7 day list`
FROM
( 
SELECT 
proj.proj_id,
proj.list_date,
proj.state,
COUNT(DISTINCT CASE 
WHEN emp.worked_date BETWEEN proj.list_date and DATE_ADD(proj.list_date, INTERVAL 6 DAY)
THEN emp.employee_id
END) AS TotalUniqEmp7Days
-- , COUNT(DISTINCT emp.employee_id) AS TotalUniqEmp
FROM project.analytics proj
LEFT JOIN employees.analytics emp ON emp.proj_id = proj.proj_id
GROUP BY proj.proj_id, proj.list_date, proj.state
) AS ProjectTotals
GROUP BY YEAR(list_date), MONTH(list_date), MONTHNAME(list_date), state;

Sql Fiddle测试可以在这里找到

Answer 2

我认为这是您想要的代码

select
p.list_date, p.state,
emp.no_of_unique_emp 
from project.analytics p
inner join (
select
t.project_id,
count(t.employee_id) as no_of_unique_emp
from (
select distinct employee_id, project_id
from employees.analytics
) t
group by t.project_id
) emp
on emp.project_id = p.project_id
where datediff (p.list_date, getdate()) <= 7