Phrase Data (TMS)

SELECT
    ssv.qps
    ,DATE(DATE_TRUNC('month', ssv.date_created)) AS date_month
    ,COUNT(DISTINCT 세그먼트_ID) AS mt_세그먼트
FROM segment_statistic_v2 ssv
WHERE
    ssv.date_created >= DATEADD('day', -365, CURRENT_DATE)
    AND ssv.date_created < CURRENT_DATE
    AND ssv.translation_origin = 'mt' -- MT에 의해 사전 번역된 세그먼트
GROUP BY
    ssv.qps
    ,DATE(DATE_TRUNC('month', ssv.date_created));

SELECT
    ssv.qps
    ,pcmv.custom_field_name
    ,pcmv.custom_field_values
    ,COUNT(DISTINCT 세그먼트_ID) AS mt_세그먼트
FROM segment_statistic_v2 ssv
JOIN job_v2 jv
    ON ssv.job_id = jv.job_id
JOIN project_custom_metadata_v2 pcmv
    ON jv.project_id = pcmv.project_id
WHERE
    ssv.date_created >= DATEADD('day', -28, CURRENT_DATE)
    AND ssv.date_created < CURRENT_DATE
    AND ssv.translation_origin = 'mt' -- MT에 의해 사전 번역된 세그먼트
GROUP BY
    ssv.qps
    ,pcmv.custom_field_name
    ,pcmv.custom_field_values;

SELECT
    ssv.qps
    ,jv.locale_pair
    ,COUNT(DISTINCT 세그먼트_ID) AS mt_세그먼트
FROM segment_statistic_v2 ssv
JOIN job_v2 jv
    ON ssv.job_id = jv.job_id
WHERE
    ssv.date_created >= DATEADD('day', -28, CURRENT_DATE)
    AND ssv.date_created < CURRENT_DATE
    AND ssv.translation_origin = 'mt' -- MT에 의해 사전 번역된 세그먼트
GROUP BY
    ssv.qps
    ,jv.locale_pair;

SELECT
    ssv.qps
    ,mtsv.machine_translate_setting_type
    ,COUNT(DISTINCT 세그먼트_ID) AS mt_세그먼트
FROM segment_statistic_v2 ssv
결합 machine_translate_setting_v2 mtsv
    ON ssv.machine_translate_setting_id = mtsv.machine_translate_setting_id
WHERE
    ssv.date_created >= DATEADD('day', -28, CURRENT_DATE)
    AND ssv.date_created < CURRENT_DATE
    AND ssv.translation_origin = 'mt' -- MT에 의해 사전 번역된 세그먼트
GROUP BY
    ssv.qps
    ,mtsv.machine_translate_setting_type;

SELECT
    QPS
    ,COUNT(DISTINCT 세그먼트_ID) AS mt_세그먼트
    ,COUNT(DISTINCT IFF(LOWER(confirmation_source) IN ('mt', 'tm', 'nt', 'ir', 'ut'), 세그먼트_ID, NULL)) AS mt_세그먼트_자동확인됨
FROM segment_statistic_v2
WHERE
    date_created >= DATEADD('day', -28, CURRENT_DATE)
    AND date_created < CURRENT_DATE
    AND translation_origin = 'mt' -- MT에 의해 사전 번역된 세그먼트
GROUP BY
    qps;

SELECT
    QPS
    ,COUNT(DISTINCT 세그먼트_ID) AS mt_세그먼트
    ,COUNT(DISTINCT IFF(COALESCE(editing_time_ms, 0) > 0, segment_id, NULL)) AS mt_segments_edited
FROM segment_statistic_v2
WHERE
    date_created >= DATEADD('day', -28, CURRENT_DATE)
    AND date_created < CURRENT_DATE
    AND translation_origin = 'mt' -- MT에 의해 사전 번역된 세그먼트
GROUP BY
    QPS;

WITH QPS_segments AS (
    SELECT
        ssv.qps
        ,SUM(words_processed) AS words_processed
    FROM segment_statistic_v2 ssv
    WHERE
        ssv.date_created >= DATEADD('day', -28, CURRENT_DATE)
        AND ssv.date_created < CURRENT_DATE
        AND ssv.translation_origin = 'mt' -- MT에 의해 사전 번역된 세그먼트
    GROUP BY
        ssv.qps
)

SELECT
    qps AS new_qps_threshold
    ,SUM(words_processed) OVER (ORDER BY QPS DESC) AS words_saved
FROM QPS_segments;