/* Dimenzi DIM_CALENDAR jsem vytvořil v příkladech je článku s dimenzemi a zde se poměrně hodí pro zajímavé ukázky */
select *
from l2.DIM_CALENDAR
LIMIT 100;

/* Ukázka agregačních funkcí s OVER PARTITION BY - agreguje i bez GROUP BY */
SELECT cal.DIM_CALENDAR_DATE, cal.FLAG_WORKING_DAY,
	/* Celková suma pracovních dní v měsíci */
	sum(cal.FLAG_WORKING_DAY) OVER (PARTITION BY cal.FLAG_WORKING_DAY) AS CNT_WORKING_DAYS, 
	/* Inkrementální suma pracovních dní v měsíci */
	sum(cal.FLAG_WORKING_DAY) OVER (PARTITION BY cal.FLAG_WORKING_DAY order by cal.DIM_CALENDAR_DATE) AS CNT_INC_WORKING_DAYS,
	/* Ukázka, že do sumy lze dát i něco složitějšího než jen sloupec - počet pracovních dní do dnešního dne včetně */
	sum(CASE WHEN cal.DIM_CALENDAR_DATE <= now()::date THEN cal.FLAG_WORKING_DAY ELSE 0 END) OVER (PARTITION BY cal.FLAG_WORKING_DAY) AS CNT_WORKING_DAYS_TO_TODAY
FROM l2.DIM_CALENDAR cal
WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-01' AND '2023-11-30' /* Listopad 2023 */
ORDER BY cal.DIM_CALENDAR_DATE;


/* Ukázka row_number() - vybere pátý pracovní den*/
SELECT *
FROM (
	SELECT cal.DIM_CALENDAR_DATE, row_number() OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS RN
	FROM l2.DIM_CALENDAR cal
	WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-01' AND '2023-11-30' /* Listopad 2023 */
		AND cal.FLAG_WORKING_DAY = 1 /* Pouze pracovní dny*/
) cal
WHERE cal.RN = 5
;


/* Ukázka funkcí rank(), dense_rank() a row_number() na stejných datech - demostrace rozdílu */
SELECT src.XNAME,
	rank() OVER (ORDER BY src.XNAME) as RNK,
	dense_rank() OVER (ORDER BY src.XNAME) as DNS_RNK,
	row_number() OVER (ORDER BY src.XNAME) as RN
FROM (VALUES ('A'), ('N'), ('D'), ('N'), ('Č'), ('Č'), ('Z')) AS src(XNAME)
;


/* Lead a lag */
SELECT cal.DIM_CALENDAR_DATE,
	lead(cal.DIM_CALENDAR_DATE) OVER (ORDER BY cal.DIM_CALENDAR_DATE) as NEXT_DAY, /* Následující den */
	lead(cal.DIM_CALENDAR_DATE, 5) OVER (ORDER BY cal.DIM_CALENDAR_DATE) as NEXT_5_DAY, /* Pátý následující den */
	lead(cal.DIM_CALENDAR_DATE, 5, '3000-01-01') OVER (ORDER BY cal.DIM_CALENDAR_DATE) as NEXT_5_DAY_NULL_VALUE, /* Pátý následující den, nahrazení NULL */
	lag(cal.DIM_CALENDAR_DATE) OVER (ORDER BY cal.DIM_CALENDAR_DATE) as PREVIOUS_DAY /* Předchozí den */
FROM l2.DIM_CALENDAR cal
WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-20' AND '2023-11-30' /* Listopad 2023 */
	AND cal.FLAG_WORKING_DAY = 1 /* Pouze pracovní dny*/
;


/* First & nth value */
SELECT cal.DIM_CALENDAR_DATE,
	first_value(cal.DIM_CALENDAR_DATE) OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS FIRST_VAL,
	nth_value(cal.DIM_CALENDAR_DATE, 5) OVER (ORDER BY cal.DIM_CALENDAR_DATE) AS FIVE_VAL, /* Hodnota se zobrazuje až od ní */
	nth_value(cal.DIM_CALENDAR_DATE, 5) OVER (ORDER BY cal.DIM_CALENDAR_DATE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FIVE_VAL_FULL /* Hodnota se zobrazí na celé množině */
FROM l2.DIM_CALENDAR cal
WHERE cal.DIM_CALENDAR_DATE BETWEEN '2023-11-01' AND '2023-11-30' /* Listopad 2023 */
	AND cal.FLAG_WORKING_DAY = 1 /* Pouze pracovní dny*/
;


/* Percentil */
SELECT
	percentile_cont(0.5) WITHIN GROUP (ORDER BY src.VAL) AS MEADIAN,
	percentile_cont(0.25) WITHIN GROUP (ORDER BY src.VAL) AS PERC_25,
	percentile_cont(0.75) WITHIN GROUP (ORDER BY src.VAL) AS PERC_75
FROM (VALUES (10), (20), (30), (40), (50)) as src(VAL);


/* String agg */
SELECT string_agg(src.XNAME, ', ' ORDER BY src.XNAME desc) as LONG_XNAME
FROM (VALUES ('Dobry den'), ('Ahoj'), ('Nashle'), ('Čau'), ('Čus')) src(XNAME);

/* Contrary to strgin agg */
SELECT unnest(string_to_array('Ahoj;Dobry den;Nashle', ';'));