SQL -使用频率(模式)计算激励

Question

​

​

我希望使用一个SQL查询来计算incentive列，并根据一个月内学生接受辅导的次数。

如果学生在一个月内在AP Math接受了两次辅导，那么导师将得到20美元，否则将获得0美元。

我不想把日期分组到一个月的总结中，所以，我想留下日期，因为这就是为什么我为每个记录分配了10美元，在每个记录中，每个月被辅导两次的学生。

Answer 1

我想你只是需要窗口功能：

select t.*,
       (case when cnt = 2 then 10 else 0 end) as incentive
from (select t.*,
             count(*) over (partition by studentid, subject, tutor, year(studentvisitdate), month(studentvisitdate) as cnt
      from t
     ) t

Answer 2

初始化数据

declare @StudentTable as table
(
    StudentVisitDate  date,
    StudentId int,
    StudentName varchar(100),
    [Subject] varchar (30),
    Tutor varchar(100),
    Incentive int
)

insert into @StudentTable
values
('2018-August-03',123,'Terry Brooks','AP Math','Shawn Green',10)
,('2018-August-04',123,'Terry Brooks','AP Science','Ted Berry',10)
,('2018-August-07',123,'Terry Brooks','Music','Shawn Green',10)
,('2018-September-03',123,'Terry Brooks','AP Math','Shawn Green',10)
,('2018-September-04',123,'Terry Brooks','AP Science','Ted Berry',10)
,('2018-September-07',123,'Terry Brooks','AP Math','Shawn Green',10)

每月获得访问次数，将数据分组

;with temp as
(
select
StudentId,  Subject,    Tutor,Month(StudentVisitDate) [month]
,max(StudentVisitDate) maxdate -- the date on which the incentive will be calculated
,Count(StudentVisitDate) [count]
from @StudentTable
Group by
StudentId,  Subject,    Tutor,Month(StudentVisitDate)
)

利用上表中的信息获取每个月的有效激励

select
s.StudentVisitDate  ,
s.StudentId ,
s.StudentName ,
s.[Subject] ,
s.Tutor ,
Case
when t.maxdate = s.StudentVisitDate -- the incentive will be applied on the maximum date
then
 s.Incentive*t.count
 else
 0
 end Incentive
from @StudentTable s
inner join temp t
on s.StudentId=t.StudentId
and s.Subject =t.Subject
and s.Tutor=t.Tutor
and Month(s.StudentVisitDate)=t.month
order by StudentVisitDate

最终产出-

StudentVisitDate    StudentId   StudentName Subject Tutor   Incentive
2018-08-03  123 Terry Brooks    AP Math Shawn Green 10
2018-08-04  123 Terry Brooks    AP Science  Ted Berry   10
2018-08-07  123 Terry Brooks    Music   Shawn Green 10
2018-09-03  123 Terry Brooks    AP Math Shawn Green 0
2018-09-04  123 Terry Brooks    AP Science  Ted Berry   10
2018-09-07  123 Terry Brooks    AP Math Shawn Green 20