仅包含某些变量的计数和

Question

我有以下返回结果的查询，但我需要从filled_orders列中排除某些变量。

整个查询：

SELECT
    order_date,
    p_category,
    issue_group,
    srt_level,
    order_count,
    filled_orders,
    total_orders,
    round(100 *(order_count / total_orders), 1) AS monthly_metric
FROM
    (
        SELECT
            order_date,
            p_category,
            issue_group,
            srt_level,
            order_count,
            CASE
                WHEN srt_level = '80'  THEN order_count
                WHEN srt_level = '100' THEN SUM(order_count) OVER(
                    PARTITION BY order_date, issue_group, p_category
                )
            END AS filled_orders,
            total_orders
        FROM
            (
                SELECT
                    order_date,
                    p_category,
                    issue_group,
                    srt_level,
                    order_count,
                    SUM(order_count) OVER(
                        PARTITION BY order_date, issue_group, p_category
                    ) AS total_orders
                FROM
                    (
                        SELECT
                            order_date,
                            p_category,
                            CASE
                                WHEN ( issue_grp = 1 ) THEN '1'
                                ELSE '2/3 '
                            END AS issue_group,
                            srt   AS srt_level,
                            COUNT(*) AS order_count
                        FROM
                            database.tcon_mv
                        WHERE
                            order_date IN (
                                '201803'
                            )
                        GROUP BY
                            p_category,
                            CASE
                                WHEN ( issue_grp = 1 ) THEN '1'
                                ELSE '2/3 '
                            END,
                            srt,
                            order_date
                    )
            )
    )
ORDER BY
    order_date,
    p_category,
    issue_group

给了我“问题”的部分：

... SELECT
                order_date,
                p_category,
                issue_group,
                srt_level,
                order_count,
                CASE
                    WHEN srt_level = '80'  THEN order_count
                    WHEN srt_level = '100' THEN SUM(order_count) OVER(
                        PARTITION BY order_date, issue_group, p_category
                    )
                END AS filled_orders,
                total_orders
            FROM ....

srt_level是一个varchar列，它的结果只有3个选项(80、100和Late)。当srt_level = ' 100‘时，我需要计数只包括80和100行的总和。

完整查询返回内容的视图，预期结果为红色。

​

​

Answer 1

我假设您只想在srt_level不是Late的情况下对order_count值求和。然后说是在你的sum中

SELECT
    order_date,
    p_category,
    issue_group,
    srt_level,
    order_count,
    CASE
        WHEN srt_level = '80'  THEN order_count
        WHEN srt_level = '100' THEN 
            SUM(CASE WHEN srt_level != 'Late' THEN order_count END) OVER(
               PARTITION BY order_date, issue_group, p_category
            )
    END AS filled_orders,
    total_orders
FROM ....