売上割り振るクエリ

code:warifuri.sql

WITH

-- 商品ごとの売上データ

uriage AS (

SELECT * FROM UNNEST(

ARRAY<STRUCT<id INT64, date DATE, item STRING, price NUMERIC>>[

(1, "2022-01-10", "ramen", 500),

(2, "2022-01-20", "soba", 1000),

(3, "2022-02-10", "soba", 600),

(4, "2022-02-20", "soba", 1000),

(5, "2022-03-05", "ramen", 2000),

(6, "2022-03-10", "soba", 900)

]

)

),

-- 売上の配分表

-- since 日から、 item ごとに ore と omae に rate の割合で配る

-- rate が改訂されるごとに追記する、(since, item) ごとの rate の合計は常に 1 になるように定義する

-- (売上日より過去で最も近い since を使う)

warifuri_rate AS (

SELECT * FROM UNNEST(

ARRAY<STRUCT<since DATE, item STRING, warifuri STRING, rate NUMERIC>>[

("2022-01-01", "ramen", "ore", 1.0),

("2022-01-01", "soba", "omae", 0.2),

("2022-01-01", "soba", "omae", 0.8),

("2022-02-01", "soba", "ore", 0.6),

("2022-02-01", "soba", "omae", 0.4),

("2022-03-01", "soba", "ore", 1.0)

]

)

)

SELECT

*,

SAFE_MULTIPLY(price, rate) AS toribun

FROM uriage

LEFT JOIN warifuri_rate USING (item)

WHERE since <= date

QUALIFY RANK() OVER (PARTITION BY id ORDER BY since DESC) = 1

-- RANK 使うと同率1位の行はすべて1がつく

https://gyazo.com/9eb1ab736ca92cecf1c2bc5535e8c92a