SQL tip

group by

• group by에 sum (case) 를 사용하는 방법

                        SELECT
                            {group_by_key} as item_id,
                            SUM(CASE WHEN {result_field_name}="success" THEN 1 ELSE 0 END) as success,
                            SUM(CASE WHEN {result_field_name}="pass" THEN 1 ELSE 0 END) as pass,
                            SUM(CASE WHEN {result_field_name}="fail" THEN 1 ELSE 0 END) as fail
                        FROM (
                            SELECT
                                {", ".join(list(field_sets_nested))},
                            FROM ranked_log 
                            WHERE rn=1
                            {f"AND {CommonJobHistoryQuery.__filter_query_builder(filter_options)}" if filter_options else ""}
                        )
                        GROUP BY {group_by_key}

• with 절로 temp table 만들어 사용하는 방법

                        WITH ranked_log AS (
                            SELECT
                                *,
                                ROW_NUMBER() OVER (PARTITION BY filter_condition_id, convert_config_id, convert_item.convert_item, ingestion_id ORDER BY created_at DESC) AS rn
                            FROM `hyperlounge-dev.dashboard.convert_job_history_v2`, UNNEST(convert_item_names) as convert_item
                            WHERE customer_code=@customer_code
                            AND ingestion_id=@ingestion_id
                        )
                        SELECT * except(rn)
                        FROM ranked_log 
                        WHERE rn=1 
                        AND source_type=@source_type
                        AND source_id=@source_id
                        AND status="success"