mysql - 如何解析group by语句中的聚合[SNOWFLAKE] SQL

Question

如何在 Snowflake 中正确重写此代码？

select account_code, date,
       sum(box_revenue_recognition_amount) as box_revenue_recognition_amount
       , sum(case when box_flg = 1 then box_sku_quantity end) as box_sku_quantity
       , sum(box_revenue_recognition_refund_amount) as box_revenue_recognition_refund_amount
       , sum(box_discount_amount) as box_discount_amount
       , sum(box_shipping_amount) as box_shipping_amount
       , sum(box_cogs) as box_cogs
       , max(invoice_number) as invoice_number
       , max(order_number) as order_number
       , min(box_refund_date) as box_refund_date
       , first (case when order_season_rank = 1 then box_type end) as box_type
       , first (case when order_season_rank = 1 then box_order_season end) as box_order_season
       , first (case when order_season_rank = 1 then box_product_name end) as box_product_name
       , first (case when order_season_rank = 1 then box_coupon_code end) as box_coupon_code
       , first (case when order_season_rank = 1 then revenue_recognition_reason end) as revenue_recognition_reason

    from dedupe_sub_user_day
    group by account_code, date

我尝试应用 first_value Snowflake documentation 中解释的窗口规则SQLCompilation 错误无济于事:...不是有效的分组依据表达式

select account_code, date,
     first_value(case when order_season_rank = 1 then box_type end) over (order by box_type ) as box_type
     first_value(case when order_season_rank = 1 then box_order_season end) over (order by box_order_season ) as box_order_season,
     first_value(case when order_season_rank = 1 then box_product_name end) over (order by box_product_name ) as box_product_name,
     first_value(case when order_season_rank = 1 then box_coupon_code end) over (order by box_coupon_code ) as box_coupon_code,
     first_value(case when order_season_rank = 1 then revenue_recognition_reason end) over (order by revenue_recognition_reason ) as revenue_recognition_reason
     , sum(box_revenue_recognition_amount) as box_revenue_recognition_amount
     , sum(case when box_flg = 1 then box_sku_quantity end) as box_sku_quantity
     , sum(box_revenue_recognition_refund_amount) as box_revenue_recognition_refund_amount
     , sum(box_discount_amount) as box_discount_amount
     , sum(box_shipping_amount) as box_shipping_amount
     , sum(box_cogs) as box_cogs
     , max(invoice_number) as invoice_number
     , max(order_number) as order_number
     , min(box_refund_date) as box_refund_date
   
    from dedupe_sub_user_day
    group by 1,2

Answer 1

First_value 不是聚合函数。但它是一个窗口函数，因此当您将它与 GROUP BY 相关使用时会出现错误。如果您想将其与组一起使用，请在其周围添加 ANY_VALUE。

这是我将在下面的 CTE 中使用的一些数据:

with data(id, seq, val) as (
    select * from values
    (1, 1, 10),
    (1, 2, 11),
    (1, 3, 12),
    (1, 4, 13),
    (2, 1, 20),
    (2, 2, 21),
    (2, 3, 22)
)

因此，要显示 FIRST_VALUE 是一个窗口函数，我们可以使用它

select *
    ,first_value(val)over(partition by id order by seq) as first_val
from data

<表类=“s-表”><标题>IDSEQVALFIRST_VAL <正文>111010121110131210141310212020222120232220

因此，如果我们按 id 进行分组，为了避免错误，我们必须用聚合值包装 FIRST_VALUE，因为它们都相等，ANY_VALUE 是一个不错的选择，而且似乎它需要位于 SQL 的另一层中:

select id
    ,count(*) as count
    ,any_value(first_val) as first_val
from (
    select *
        ,first_value(val)over(partition by id order by seq) as first_val
    from data
)
group by 1
order by 1;

ID |COUNT |FIRST_VAL1 |4 |102 |3 |20

现在 MAX 可以很有趣地用于与 ROW_NUMBER() 相关的地方来选择最佳值:

select id
    ,count(*) as count
    ,max(first_val) as first_val
from (
    select *
        ,row_number() over (partition by id order by seq) as rn
        ,iff(rn=1, val, null) as first_val
    from data
)
group by 1
order by 1;

但这几乎比 ANY_VALUE 解决方案更复杂，但我觉得性能会更好，但如果它们具有相同程度的性能，我总是会选择对您和您的团队可读，而不是较小的性能差异。