본문 바로가기
카테고리 없음

레슨

by 신방동불주먹 2024. 7. 22.
반응형
SELECT trr.merge_study_cnt, sub.*
FROM 
(SELECT last_total.PROGRAM1, last_total.LECTURE_NAME, last_total.TITLE,last_total.UNIT_SEQ, AVG(last_total.percent) AS study_rate
		,COUNT(*) as member_cnt
		,COUNT(CASE WHEN last_total.percent > 0 THEN 1 ELSE NULL END) AS study_cnt --학습자수
		,ROUND(NVL(COUNT(CASE WHEN last_total.percent > 0 THEN 1 ELSE NULL END) / NULLIF(COUNT(*),0),0) * 100,2) AS member_rate  --현원대비
FROM 
	(
	SELECT total.LECTURE_SEQ, total.LECTURE_NAME, total.UNIT_SEQ, total.TITLE, total.USER_ID, total.PROGRAM1,
	        ROUND(NVL( ( ( NVL(total.listening_percent, 0)  + NVL(total.speaking_percent, 0) +  NVL(total.reading_percent, 0)  + NVL(total.vocab_percent, 0)  + NVL(total.writing_percent, 0)  + NVL(total.test_percent, 0))
	            / DECODE(total.listening_total + total.speaking_total + total.reading_total + total.vocab_total + total.writing_total + total.unit_test_total, 0 , NULL,   
	            total.listening_total + total.speaking_total + total.reading_total + total.vocab_total+ total.writing_total + total.unit_test_total)  
	            ), 0),2) as percent 
	FROM
		(
		SELECT main.LECTURE_SEQ, main.LECTURE_NAME, main.UNIT_SEQ, main.TITLE ,progress.USER_ID, main.PROGRAM1
			, DECODE(NVL((sum(main.listening_total) + sum(main.listening_quiz_total)), 0), 0, 0, 1) as listening_total
			, DECODE(NVL((sum(main.speaking_total) + sum(main.speaking_trainer_total)), 0), 0, 0, 1) as speaking_total
			, DECODE(NVL((sum(main.reading_quiz_total) + sum(main.reading_total)), 0), 0, 0, 1) as reading_total
			, DECODE(NVL((sum(main.vocab_total) + sum(main.vocab_quiz_total)), 0), 0, 0, 1) as vocab_total
	        , DECODE(NVL((sum(main.writing_total) ), 0), 0, 0, 1) as writing_total
	        , DECODE(NVL((sum(main.unit_test_total) ), 0), 0, 0, 1) as unit_test_total
	        , least((sum(progress.listening_count) + sum(progress.listening_quiz_count))  /  DECODE((sum(main.listening_total) + sum(main.listening_quiz_total)) , 0, NULL, (sum(main.listening_total) + sum(main.listening_quiz_total)))   * 100, 100) as listening_percent
	        , least((sum(progress.speaking_count) + sum(progress.speaking_trainer_count))  / DECODE((sum(main.speaking_total) + sum(main.speaking_trainer_total)) , 0, NULL, (sum(main.speaking_total) + sum(main.speaking_trainer_total)))  * 100, 100) as speaking_percent 
	        , least((sum(progress.reading_count) + sum(progress.reading_quiz_count))  / DECODE((sum(main.reading_quiz_total) + sum(main.reading_total)) , 0, NULL,(sum(main.reading_quiz_total) + sum(main.reading_total))) * 100, 100) as reading_percent
	        , least((sum(progress.vocab_count) + sum(progress.vocab_quiz_count))  / DECODE((sum(main.vocab_total) + sum(main.vocab_quiz_total)) , 0, NULL, (sum(main.vocab_total) + sum(main.vocab_quiz_total))) * 100, 100) as vocab_percent
	        , least((sum(progress.writing_count))  / DECODE((sum(main.writing_total)) , 0, NULL, (sum(main.writing_total))) * 100, 100) as writing_percent
	        , least((sum(progress.unit_test_count))  / DECODE((sum(main.unit_test_total)) , 0, NULL, (sum(main.unit_test_total))) * 100, 100) as test_percent 
			FROM 
				(SELECT unit.*, lecseq.LECTURE_NAME, lecseq.PROGRAM1
				FROM 
					(
					SELECT DISTINCT lecture.LECTURE_SEQ,lecture.LECTURE_NAME,course.PROGRAM1
					FROM
						(SELECT program1.PROGRAM1, tc.COURSE_SEQ
							FROM T_COURSE tc
							INNER JOIN T_PROGRAM1 program1 ON tc.PROGRAM1_SEQ = program1.PROGRAM1_SEQ 
							WHERE tc.COMPANY_ID = 3
							AND tc.PROGRAM1_SEQ = 3
--							AND tc."YEAR" = TO_CHAR(SYSDATE , 'YYYY')
							AND tc.START_DATE <= '2024-07-21'
							AND tc.END_DATE >= '2024-07-21'
						) course
					INNER JOIN T_COURSE_PERIOD period ON course.COURSE_SEQ = period.COURSE_SEQ
					INNER JOIN T_LECTURE lecture ON lecture.LECTURE_SEQ = period.F_LECTURE_SEQ
					) lecseq
				INNER JOIN T_UNIT unit ON unit.LECTURE_SEQ = lecseq.LECTURE_SEQ) main
				INNER JOIN T_UNIT_PROGRESS progress ON main.UNIT_SEQ = progress.UNIT_SEQ
				GROUP BY main.LECTURE_SEQ, main.LECTURE_NAME, main.UNIT_SEQ, main.TITLE, main.PROGRAM1,progress.USER_ID) total
	) last_total
GROUP BY last_total.PROGRAM1,last_total.LECTURE_NAME,last_total.TITLE,last_total.UNIT_SEQ) sub
INNER JOIN 
(SELECT COUNT(CASE WHEN last_total.percent > 0 THEN 1 ELSE NULL END) AS merge_study_cnt, last_total.UNIT_SEQ --학습자수
FROM 
	(
	SELECT total.LECTURE_SEQ, total.LECTURE_NAME, total.UNIT_SEQ, total.TITLE, total.USER_ID, total.PROGRAM1,
	        ROUND(NVL( ( ( NVL(total.listening_percent, 0)  + NVL(total.speaking_percent, 0) +  NVL(total.reading_percent, 0)  + NVL(total.vocab_percent, 0)  + NVL(total.writing_percent, 0)  + NVL(total.test_percent, 0))
	            / DECODE(total.listening_total + total.speaking_total + total.reading_total + total.vocab_total + total.writing_total + total.unit_test_total, 0 , NULL,   
	            total.listening_total + total.speaking_total + total.reading_total + total.vocab_total+ total.writing_total + total.unit_test_total)  
	            ), 0),2) as percent 
	FROM
		(
		SELECT main.LECTURE_SEQ, main.LECTURE_NAME, main.UNIT_SEQ, main.TITLE ,progress.USER_ID, main.PROGRAM1
			, DECODE(NVL((sum(main.listening_total) + sum(main.listening_quiz_total)), 0), 0, 0, 1) as listening_total
			, DECODE(NVL((sum(main.speaking_total) + sum(main.speaking_trainer_total)), 0), 0, 0, 1) as speaking_total
			, DECODE(NVL((sum(main.reading_quiz_total) + sum(main.reading_total)), 0), 0, 0, 1) as reading_total
			, DECODE(NVL((sum(main.vocab_total) + sum(main.vocab_quiz_total)), 0), 0, 0, 1) as vocab_total
	        , DECODE(NVL((sum(main.writing_total) ), 0), 0, 0, 1) as writing_total
	        , DECODE(NVL((sum(main.unit_test_total) ), 0), 0, 0, 1) as unit_test_total
	        , least((sum(progress.listening_count) + sum(progress.listening_quiz_count))  /  DECODE((sum(main.listening_total) + sum(main.listening_quiz_total)) , 0, NULL, (sum(main.listening_total) + sum(main.listening_quiz_total)))   * 100, 100) as listening_percent
	        , least((sum(progress.speaking_count) + sum(progress.speaking_trainer_count))  / DECODE((sum(main.speaking_total) + sum(main.speaking_trainer_total)) , 0, NULL, (sum(main.speaking_total) + sum(main.speaking_trainer_total)))  * 100, 100) as speaking_percent 
	        , least((sum(progress.reading_count) + sum(progress.reading_quiz_count))  / DECODE((sum(main.reading_quiz_total) + sum(main.reading_total)) , 0, NULL,(sum(main.reading_quiz_total) + sum(main.reading_total))) * 100, 100) as reading_percent
	        , least((sum(progress.vocab_count) + sum(progress.vocab_quiz_count))  / DECODE((sum(main.vocab_total) + sum(main.vocab_quiz_total)) , 0, NULL, (sum(main.vocab_total) + sum(main.vocab_quiz_total))) * 100, 100) as vocab_percent
	        , least((sum(progress.writing_count))  / DECODE((sum(main.writing_total)) , 0, NULL, (sum(main.writing_total))) * 100, 100) as writing_percent
	        , least((sum(progress.unit_test_count))  / DECODE((sum(main.unit_test_total)) , 0, NULL, (sum(main.unit_test_total))) * 100, 100) as test_percent 
			FROM 
				(SELECT unit.*, lecseq.LECTURE_NAME, lecseq.PROGRAM1
				FROM 
					(
					SELECT DISTINCT lecture.LECTURE_SEQ,lecture.LECTURE_NAME,course.PROGRAM1
					FROM
						(SELECT program1.PROGRAM1, tc.COURSE_SEQ
							FROM T_COURSE tc
							INNER JOIN T_PROGRAM1 program1 ON tc.PROGRAM1_SEQ = program1.PROGRAM1_SEQ 
							WHERE tc.COMPANY_ID = 3
							AND tc.PROGRAM1_SEQ = 3
						) course
					INNER JOIN T_COURSE_PERIOD period ON course.COURSE_SEQ = period.COURSE_SEQ
					INNER JOIN T_LECTURE lecture ON lecture.LECTURE_SEQ = period.F_LECTURE_SEQ
					) lecseq
				INNER JOIN T_UNIT unit ON unit.LECTURE_SEQ = lecseq.LECTURE_SEQ) main
				INNER JOIN T_UNIT_PROGRESS progress ON main.UNIT_SEQ = progress.UNIT_SEQ
				GROUP BY main.LECTURE_SEQ, main.LECTURE_NAME, main.UNIT_SEQ, main.TITLE, main.PROGRAM1,progress.USER_ID) total
	) last_total
GROUP BY last_total.PROGRAM1,last_total.LECTURE_NAME,last_total.TITLE,last_total.UNIT_SEQ) trr
ON sub.UNIT_SEQ = trr.UNIT_SEQ
ORDER BY trr.UNIT_SEQ;
반응형
저작자표시 (새창열림)

티스토리툴바