데이터베이스 개발자 질문과 답변 게시판
데이터베이스 개발/운영 관련 질문과 답변을 올리는 게시판입니다. 궁금하신 내용을 이곳에서 문의하시면 SQLER 분들의 답변을 받으실 수 있습니다. 문의를 하실때에는 최근 작업하신 특이 사항이나, 장애 발생 전 상황을 상세히 올려 주시면 답글을 적어주시는 SQLER분들의 답변이 더 정확할 수 있으니 도움 되시길 바랍니다. 쿼리 문의일 경우, 실제 문제가 재현되는 테이블생성, 샘플데이터 생성 쿼리를 함께 올려 주시면 더 빠르고 정확한 쿼리 문의 응답이 가능합니다.
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