SparkSQL如何优化最近N天下单金额最大值

需求背景

已知用户订单金额表user_order_amt_1d，计算最近N天用户下单金额最大值（N = 3、30、180）

初级解决方案

• 这个需求本身并不难，只需分别求出最近3天用户金额最大值、最近30天用户金额最大值、最近180用户金额最大值，然后进行关联即可

spark.sql(
  s"""
     |select
     |  t1.user_id,
     |  t1.max_amt_3d,
     |  t2.max_amt_30d,
     |  t3.max_amt_180d
     |from (
     |select user_id, max(amt) as max_amt_3d
     |from user_order_amt_1d
     |where dt <= '$dt' and dt >= '${dt-2}'
     |group by user_id
     |) t1
     |JOIN (
     |select user_id, max(amt) as max_amt_30d
     |from user_order_amt_1d
     |where dt <= '$dt' and dt >= '${dt-29}'
     |group by user_id
     |) t2
     |on t1.user_id = t2.user_id
     |JOIN (
     |select user_id, max(amt) as max_amt_180d
     |from user_order_amt_1d
     |where dt <= '$dt' and dt >= '${dt-179}'
     |group by user_id
     |) t3
     |on t1.user_id = t3.user_id
     |""".stripMargin).show

存在的问题

1、【数据量大】同一个数据源被读取了多次，数据量剧增

2、【Shuffle多】多张表之间JOIN，存在多次Shuffle

3、【扩展性差】如果业务需要计算最近90天或者360天用户下单金额最大值时，需重新修改代码

进阶解决方案

• 针对上述问题1和问题2，对SQL可进行如下优化：

spark.sql(
  s"""
     |select
     |  user_id,
     |  max(case when dt >= '${dt-2}' then amt else 0 end) as max_amt_3d,
     |  max(case when dt >= '${dt-29}' then amt else 0 end) as max_amt_30d,
     |  max(case when dt >= '${dt-179}' then amt else 0 end) as max_amt_180d
     |from user_order_amt_1d
     |where dt <= '$dt' and dt >= '${dt-179}'
     |group by user_id
     |""".stripMargin).show

存在的问题

• 此方案，数据源只会被读取一次，同时避免了多张表的JOIN操作，但是 user_order_amt_1d是亿级用户表，我们每日扫描最近180天的用户数据，显然读取的数据量仍然很大，进行shuffle仍然效率低下

终极解决方案

• 如何避免每日读取最近180天的用户数据？
• 我们可以保存昨日计算的状态，然后和今日数据进行合并，具体操作如下：
• 设计一个用户下单金额累计表，存储最近180天下单金额的数组
• 使用array_max函数+slice函数即可获取最近N天下单金额中的最大值（N <= 180）

// 1、存储最近180天下单金额
spark.sql(
  s"""
    |insert overwrite table user_order_amt_td partition(dt = '$dt')
    |select
    | t1.user_id,
    | case when size(t1.amt_180d_arr) >= 180 then concat(array(t2.amt), slice(t1.amt_180d_arr, 1, 179))
    |   else concat(array(t2.amt), t1.amt_180d_arr)  end as amt_180d_arr
    |from (
    |select user_id, amt_180d_arr
    |from user_order_amt_nd
    |where dt = '${dt-1}
    |) t1
    |join (
    |select user_id, amt
    |from user_order_amt_1d
    |where dt = '$dt'
    |) t2
    |on t1.user_id = t2.user_id
    |""".stripMargin).show
// 2、求解最近3、30、180天下单金额最大值
spark.sql(
  s"""
    |select
    | user_id,
    | array_max(slice(amt_180d_arr, 1, 3)) as max_amt_3d,
    | array_max(slice(amt_180d_arr, 1, 30)) as max_amt_30d,
    | array_max(amt_180d_arr) as max_amt_180d
    |from user_order_amt_td
    |where dt = '$dt'
    |""".stripMargin).show

思考

以上三种方案都未能解决扩展性差的问题，我们是否可以在不改动业务代码的前提下，完成近N天用户下单金额最大值的扩展呢？（N为任意值）

如果当前架构解决不了，那就加一张表，假设还解决不了，怎么办？

下期我会详细分享如何通过配置化的方式来求解最近N天下单金额最大值