관련 코호트 분석 - 누적 계산

누적 계산

• id별로 term sart를 오름차순 했을 때 첫 번째 term type값
• id별로 가장 작은 term start 날짜
• id별로 가장 작은 term start 날짜 + 10년
• legislators_term 테이블 JOIN
• B 테이블의 term start가 A 테이블의 first term과 10년 후 사이에 있는 날짜
• century, first type별 그루핑

SELECT
	DATE_PART('century', A.first_term) AS century,
	first_type,
	COUNT(DISTINCT A.id_bioguide) AS cohort,
	COUNT(B.term_start) AS terms
FROM (
	SELECT
		DISTINCT id_bioguide,
		FIRST_VALUE(term_type) OVER(PARTITION BY id_bioguide ORDER BY term_start ASC) AS first_type,
		MIN(term_start) OVER(PARTITION BY id_bioguide) AS first_term,
		MIN(term_start) OVER(PARTITION BY id_bioguide) + INTERVAL '10 years' AS first_plus_10
	FROM legislators_terms
) AS A LEFT JOIN legislators_terms AS B ON A.id_bioguide = B.id_bioguide
	AND B.term_start BETWEEN A.first_term AND A.first_plus_10
GROUP BY 1, 2;

• id 개수로 나누어 평균 계산
• CASE 구문을 이용해 피봇

SELECT
	century,
	MAX(CASE WHEN first_type = 'rep' THEN cohort END) AS rep_cohort,
	MAX(CASE WHEN first_type = 'rep' THEN terms_per_leg END) AS avg_rep_terms,
	MAX(CASE WHEN first_type = 'sen' THEN cohort END) AS sen_cohort,
	MAX(CASE WHEN first_type = 'sen' THEN terms_per_leg END) AS avg_sen_terms 
FROM (
	SELECT
		DATE_PART('century', A.first_term) AS century,
		first_type,
		COUNT(DISTINCT A.id_bioguide) AS cohort,
		COUNT(B.term_start) AS terms,
		ROUND(COUNT(B.term_start) * 1.0 / COUNT(DISTINCT A.id_bioguide), 3) AS terms_per_leg
	FROM (
		SELECT
			DISTINCT id_bioguide,
			FIRST_VALUE(term_type) OVER(PARTITION BY id_bioguide ORDER BY term_start ASC) AS first_type,
			MIN(term_start) OVER(PARTITION BY id_bioguide) AS first_term,
			MIN(term_start) OVER(PARTITION BY id_bioguide) + INTERVAL '10 years' AS first_plus_10
		FROM legislators_terms
	) AS A LEFT JOIN legislators_terms AS B ON A.id_bioguide = B.id_bioguide
		AND B.term_start BETWEEN A.first_term AND A.first_plus_10
	GROUP BY 1, 2
) AS result
GROUP BY 1