3명의 학생을 기준으로
학번 년도 학기
1 2019 1
1 2019 2
1 2020 1
2 2019 1
2 2020 1
3 2018 1
3 2018 2
3 2019 1
3 2019 2
형태의 데이터를 학번별로 연속된 년도/학기수를 구하고 싶습니다.
**** 예상결과 ****
학번 연속된 년도학기 수
1 3
2 0
3 4
도저히 방법이 생각나지 않아서 문의드립니다..
Comment 2
-
지영아빠
2020.07.23 11:00
-
약은 중
2020.07.27 13:58
WITH DATA_ORIGIN AS (select 1 as no,2019 as yy,1 as gi union allselect 1,2019,2 union allselect 1,2020,1 union allselect 1,2017,2 union allselect 1,2018,1 union allselect 2,2019,1 union allselect 2,2020,1 union allselect 3,2018,1 union allselect 3,2018,2 union allselect 3,2019,1 union allselect 3,2019,2),DATA_1 AS (SELECT NO,ROW_NUMBER() OVER(PARTITION BY NO ORDER BY YY,GI) RN,YY + (GI - 1) * 0.5 HACK_NUM,YY + (GI - 1) * 0.5 - LAG(YY + (GI - 1) * 0.5) OVER(PARTITION BY NO ORDER BY YY,GI) DIFF_HACK_NUM,YY,GIFROM DATA_ORIGIN),CTE AS(SELECT NO,YY,GI,HACK_NUMFROM DATA_1WHERE DIFF_HACK_NUM IS NULL OR DIFF_HACK_NUM > 0.5UNION ALLSELECT A.NO,A.YY,A.GI,B.HACK_NUMFROM CTE A,DATA_1 BWHERE A.NO = B.NOAND A.HACK_NUM + 0.5 = B.HACK_NUM)SELECT NO,CASE WHEN CNT > 1 THEN CNT ELSE 0 END CNTFROM (SELECT NO,YY,GI,COUNT(*) CNTFROM CTEGROUP BY NO,YY,GI) AGROUP BY NO,CASE CNT WHEN 1 THEN '' ELSE YY END,CASE CNT WHEN 1 THEN '' ELSE GI END,CASE WHEN CNT > 1 THEN CNT ELSE 0 ENDORDER BY 1
참고요..
학기가 1, 2만 있는 binary digit이라
년도의 두자리와 학기를 decimal -> binary
두수를 연결한 binary를 decimal로 만듦.
그리고 연속된 수치인지 판단하는 쿼리 만듦.
-- decimal 2 binary
CREATE FUNCTION [dbo].[Dec2Bin]
(
@Input bigint
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @Output varchar(255) = ''
IF @Input = 0 BEGIN
Set @Output = 0
END ELSE BEGIN
WHILE @Input > 0 BEGIN
SET @Output = @Output + CAST((@Input % 2) AS varchar)
SET @Input = @Input / 2
END
END
RETURN REVERSE(@Output)
END
-- binary 2 decimal
CREATE FUNCTION [dbo].[Bin2Dec]
(
@Input varchar(255)
)
RETURNS bigint
AS
BEGIN
DECLARE @Cnt tinyint = 1
DECLARE @Len tinyint = LEN(@Input)
DECLARE @Output bigint = CAST(SUBSTRING(@Input, @Len, 1) AS bigint)
WHILE(@Cnt < @Len) BEGIN
SET @Output = @Output + POWER(CAST(SUBSTRING(@Input, @Len - @Cnt, 1) * 2 AS bigint), @Cnt)
SET @Cnt = @Cnt + 1
END
RETURN @Output
END
-- 샘플
create table #t ( no int, yy int, gi int) ;
insert into #t
select 1 as no,2019 as yy,1 as gi union all
select 1,2019,2 union all
select 1,2020,1 union all
select 2,2019,1 union all
select 2,2020,1 union all
select 3,2018,1 union all
select 3,2018,2 union all
select 3,2019,1 union all
select 3,2019,2
-- 최종 : 결과가 no=2에는 결과가 1인게 나와야 no=1은 3, no=3은 4가 될듯.
-- 아니라면 casing하시면 될 것 같음요..
with tmp as (
SELECT
no,
dbo.Bin2Dec(dbo.dec2bin(right(yy, 2))+ dbo.dec2bin(gi-1)) as serial,
row_number() OVER(partition BY no ORDER BY yy + gi) as d
FROM #t
)
select
no,
min(serial) as from_ ,
max(serial) as to_,
count(*) as cnt
from tmp a
group by no, serial-d
order by no