用于比较相同样本大小的售前和售后的单元查询或SQL查询

Question

我有一个表，其中的列为employeeIDs (string)、performance (Int)、date(string)，以及标志帐户(String)(如果订阅了)(订阅后的account = 'yes‘和订阅前的'no’)。

不同的员工在不同的日期订阅，

pre =订阅前post =订阅后

需要计算他们每4天前的总绩效评级和每一岗位4天的业绩评级总和，从他们订阅之日起算。

----------1-----2----3---4--|---4---3---2----1

表如下所示(为便于理解，每天之间留出空间)表包含每个员工每天的事务行。

((代码段只是使表结构变得清晰))

​

employeeId | performance rating | account | date 
--------------------------------------------------------      
sam            3.2                  no      2013-9-15  
charlie        3.1                  no      2013-9-15  
john           2.1                  no      2013-9-15  

sam            4.1                  yes     2013-9-16  
charlie        5.1                  no      2013-9-16  
john           2.1                  no     2013-9-16  

sam            5.3                  yes     2013-9-17  
charlie        1.4                  no      2013-9-17  
john           6.3                  yes     2013-9-17  

sam            5.3                  yes     2013-9-18  
charlie        1.4                  no      2013-9-18
john           6.3                  yes     2013-9-18

sam            5.3                  yes     2013-9-19
charlie        1.4                  yes      2013-9-19
john           8.3                  yes     2013-9-19

sam            6.3                  yes     2013-9-20
charlie        7.4                  yes      2013-9-20
john           9.3                  yes     2013-9-20

​

期望输出(数字只是用于样本，而不是计算出来的)

​

DAY            sum performance rating
pre 1st day    10.0
pre 2nd day    13.9
pre 3rd day    24.9
pre 4th day    12.4       
post 1st day   16.8
post 2nd day   14.6
post 3rd day   17.2
post 4th day   12.8

​

ANy帮助是appreciated..tried的很多方法，但仍然无法弄清楚..。

Answer 1

您几乎可以使用子查询和非完全连接在标准SQL中完成此操作：

select ta.employeeId,
       avg(case when t2.date < ta.date then t2.rating end) as beforeRating,
       avg(case when t2.date > ta.date then t2.rating end) as afterRating
from (select t.employeeId, min(date) as acctdate
      from table t
      group by t.employeeId
     ) ta join
     table t2
     on ta.employeeId = t2.employeeId and
        t2.date between ta.acctdate - 4 and ta.acctdate + 4 -- Note:  date arithmetic depends on the database
group by ta.employeeId;

唯一的非标准部分是日期算法。您需要以适合您的数据库的方式来表达这一点。

编辑：

如果你想要的是白天的结果而不是员工

select datediff(ta.acctdate, t2.date), avg(t2.rating) as avgrating
from (select t.employeeId, min(date) as acctdate
      from table t
      group by t.employeeId
     ) ta join
     table t2
     on ta.employeeId = t2.employeeId and
        t2.date between ta.acctdate - 4 and ta.acctdate + 4 -- Note:  date arithmetic depends on the database
group by datediff(ta.acctdate, t2.date);