For a comprehensive example of grouping function?

select PQ.questionid,PQ.ID,PQ.paperScoreID,PQ.spendTime,PQ.ansResult,PQ.isDone,ROW_NUMBER() OVER(PARTITION BY PQ.questionid ORDER BY PQ.ID asc) AS rowNum from QZ_MEMBER_PAPER_SCORE PS with(nolock)
         left join QZ_MEMBER_PAPER_QUESTION PQ with(nolock) on ps.paperScoreID=PQ.paperScoreID
         where PS.userID=10106 and PS.courseID=51 and PQ.questionID in (41,43)


   
Query results
   questionid  id   paperScoreid  spendTime  ansResult  isDone  rowNum
---------------------------------------------------------------------
41	443	208	58	0	1	1
41	446	209	60	0	1	2
41	449	210	8		0	3
41	452	211	0		0	4
41	561	253	0	0	1	5
41	564	254	0		0	6
41	567	255	0	0	1	7
41	570	256	0		0	8
41	573	257	499	0	1	9
43	444	208	2	0	1	1
43	447	209	2	0	1	2
43	450	210	635		0	3
43	453	211	0		0	4
43	562	253	8	0	1	5
43	565	254	0		0	6
43	568	255	0	0	1	7
43	571	256	0		0	8
43	574	257	0		0	9
   



         QuestionID - ID
         FirstDone -- the first isDone for each question(firstDone=IsDone()==0?-1:AnsResult()==1 ? 1 : 0)
         LastDone - the last one isDone for each question(lastDone=IsDone()==0?-1:AnsResult()==1 ? 1 : 0)
         percent -- (ansResult += ansResult ==1 ?1:0)/This problem is the number of records
         SpendTime  -- (SpendTime+=SpendTime)/This problem is the number of records
         
         
         
         How to realize the? 
         
         'questionID','firstDone','lastDone','percent','SpendTime'
         --------------------------------------------------------------
         41            0           0          0%         625/9
         43            0           -1         0%         647/9         
         

Started by Egbert at November 19, 2016 - 11:53 AM

questionid id paperScoreid spendTime ansResult isDone rowNum
41 443 208 58 0 1 1
41 446 209 60 0 1 2
41 449 210 8 null 0 3
41 452 211 0 null 0 4
41 561 253 0 0 1 5
41 564 254 0 null 0 6
41 567 255 0 0 1 7
41 570 256 0 null 0 8
41 573 257 499 0 1 9
43 444 208 2 0 1 1
43 447 209 2 0 1 2
43 450 210 635 null 0 3
43 453 211 0 null 0 4
43 562 253 8 0 1 5
43 565 254 0 null 0 6
43 568 255 0 0 1 7
43 571 256 0 null 0 8
43 574 257 0 null 0 9

Posted by Egbert at November 23, 2016 - 12:03 PM



questionid  id   paperScoreid  spendTime  ansResult  isDone  rowNum
41	443	208	58	0	1	1
41	446	209	60	0	1	2
41	449	210	8	null	0	3
41	452	211	0	null	0	4
41	561	253	0	0	1	5
41	564	254	0	null	0	6
41	567	255	0	0	1	7
41	570	256	0	null	0	8
41	573	257	499	0	1	9
43	444	208	2	0	1	1
43	447	209	2	0	1	2
43	450	210	635	null	0	3
43	453	211	0	null	0	4
43	562	253	8	0	1	5
43	565	254	0	null	0	6
43	568	255	0	0	1	7
43	571	256	0	null	0	8
43	574	257	0	null	0	9

Posted by Egbert at December 02, 2016 - 12:50 PM

try this,
with t as
(select PQ.questionid,PQ.ID,PQ.paperScoreID,PQ.spendTime,PQ.ansResult,PQ.isDone,
        ROW_NUMBER() OVER(PARTITION BY PQ.questionid ORDER BY PQ.ID asc) AS rowNum,
        ROW_NUMBER() OVER(PARTITION BY PQ.questionid ORDER BY PQ.ID desc) AS rowNum2
  from QZ_MEMBER_PAPER_SCORE PS with(nolock)
  left join QZ_MEMBER_PAPER_QUESTION PQ with(nolock) on ps.paperScoreID=PQ.paperScoreID
  where PS.userID=10106 and PS.courseID=51 and PQ.questionID in (41,43)
)
select a.questionid,
       (select case when d.isDone=0 then -1 
                    else case when d.ansResult=1 then 1 else 0 end end 
        from t d
        where d.questionid=a.questionid and d.rowNum=1) 'firstDone',
       (select case when d.isDone=0 then -1 
                    else case when d.ansResult=1 then 1 else 0 end end 
        from t d
        where d.questionid=a.questionid and d.rowNum2=1) 'lastDone',
       rtrim(sum(a.ansResult)/count(1))+'%' 'percent',
       rtrim(sum(a.spendTime))+'/'+rtrim(count(1)) 'SpendTime'
from t a
group by a.questionid

Posted by Blake at December 15, 2016 - 1:05 PM

This is not:
;with t
as
(
select 
PQ.questionid,PQ.ID,PQ.paperScoreID,
PQ.spendTime,PQ.ansResult,PQ.isDone,

ROW_NUMBER() OVER(PARTITION BY PQ.questionid ORDER BY PQ.ID asc) AS rowNum,
count(id) over(partition by PQ.questionid) as id_count,
sum(PQ.ansResult) over(partition by PQ.questionid) as sum_ansResult,
sum(PQ.spendTime) over(partition by PQ.questionid) as sum_spendTime

from QZ_MEMBER_PAPER_SCORE PS with(nolock)
left join QZ_MEMBER_PAPER_QUESTION PQ with(nolock) 
       on ps.paperScoreID=PQ.paperScoreID
       
where PS.userID=10106 and 
      PS.courseID=51 and 
      PQ.questionID in (41,43)
)

select questionid,

       (select isDone from t t2 
        where t1.questionid = t2.questionid 
              and t2.rownum = 1) as firstDone,
       
       (select isDone from t t2 
        where t1.questionid = t2.questionid 
              and t2.rownum = t2.id_count) as lastDone,
              
       sum_ansResult * 1.0 / id_count,
       sum_spendTime * 1.0 / id_count  
from t t1 
 

Posted by Francis at December 28, 2016 - 1:19 PM