【BigQuery】ロット単位の必要数換算で前回の余りを考慮する方法

Share this for your friends.

SQLで前回の余りを考慮する方法がわからない

データの分析や処理に便利なBigQuery(BQ)。

ではBQでロット単位生産商品の必要ロット数は計算できるのか。

必要な個数をロットの単位数量で割り算して余りを繰り上げればよくない？

それじゃ常に余りが出て端数在庫が積み上がるよね

そうか。そしたらどうすればいいんだろう。

そこで今回はBigQueryでロット単位の必要数換算で前回の余りを考慮する方法について紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

記事を読むメリット

BigQueryでロット単位の必要数換算で前回の余りを考慮する方法がわかる

ロット単位問題の例

まずロット単位問題について説明する。

ロットは生産や仕入れの最小単位のこと。

ロット管理（ロットかんり）とは、仕入・製造等の業務で発生する製品単位（ロット）ごとに製品を管理すること。

ロット管理 - Wikipedia

イメージ的には24本箱詰めされたジュース。

24本のセットを１つのロット単位として扱うイメージ。

このロット単位で製造するが、発注は1個単位で発生する場合、必要数を満たすようにロット単位で製造すると余りが発生する。

24本単位でしか製造できないのに30本発注がかかったら 24x2=48 本製造して18本余るってことね

そして余りはそのままというわけにはいかないので、次回の出荷に回す。

次にさらに30本発注がかかったら24x2=48本製造するとまた余っちゃうので、前回の余り18本があるから24本だけ製造すれば 18＋24－30 で余りは12本で済むわけね。

このような計算をBigQueryでできるのかというのが今回の問題である。

データ準備

ではまずデータを準備する。

以下のCSVを lot_problem_sample というテーブルとして取り込む。

product_code,lot_pcs,time_order,demand_pcs
a,10,1,11
a,10,2,12
a,10,3,5
a,10,4,0
a,10,5,6
a,10,6,10
a,10,7,8
a,10,8,9
a,10,9,9
a,10,10,9
b,12,1,11
b,12,2,12
b,12,3,5
b,12,4,0
b,12,5,6
b,12,6,10
b,12,7,8
b,12,8,9
b,12,9,9
b,12,10,9
c,15,1,11
c,15,2,12
c,15,3,5
c,15,4,0
c,15,5,6
c,15,6,10
c,15,7,8
c,15,8,9
c,15,9,9
c,15,10,9

データの意味は以下の通り。

product_code:商品コード
lot_pcs:１ロットの個数
time_order：時間の順
demand_pcs：生産要求のあった個数

例えば１行目の a,10,1,11 は10個単位で生産している商品Aを1日目に11個の生産要求があったという意味になる。

a,10,2,12 はAを次の日（２日目）に12個生産要求・・・といった感じである。

なので1日目は2ロット分の20個生産するが、2日目は余りの9個があるので1ロットだけ生産することになる。

BigQueryで前回の余りを考慮する方法

BigQueryで前回の余りを考慮する方法を考えると難しい。

無理ってこと？

考え方を変える必要があります

前回の余りを考慮しようとすると、余りと生産するロット数が相互に影響し合うのでSQLで表現するのが難しくなる。

そこで考え方を変える。

余りを計算するのではなく、トータルで必要なロット数を算出する。

SELECT
*,
#過去からのトータル要求数
IFNULL(
    SUM(demand_pcs )
    OVER (PARTITION BY product_code
    ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) AS total_demand_pcs,
#トータル要求数に対する必要なロット数
CEIL(
    IFNULL(
        SUM(demand_pcs )
        OVER (PARTITION BY product_code
        ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0)
        / lot_pcs) AS total_demand_lot
FROM
project.dataset.lot_problem_sample
ORDER BY
product_code,
time_order

するとこのような結果が得られる。

product_code	lot_pcs	time_order	demand_pcs	total_demand_pcs	total_demand_lot
a	10	1	11	11	2
a	10	2	12	23	3
a	10	3	5	28	3
a	10	4	0	28	3
a	10	5	6	34	4
a	10	6	10	44	5
a	10	7	8	52	6
a	10	8	9	61	7
a	10	9	9	70	7
a	10	10	9	79	8

ここで算出した各回までのトータルで必要なロット数から、直前のトータルロット数を引くと、その回で必要なロット数がわかる。

SELECT
product_code as product,
lot_pcs,
time_order as time,
demand_pcs as demand,
total_demand_pcs as total,
total_demand_lot as total_lot,
lot_pcs * total_demand_lot - total_demand_pcs AS surplus,
# ここまでのトータルで必要なロット数 - 直前までに必要なトータルロット数 = この回に必要なロット数
total_demand_lot
- IFNULL(LAG (total_demand_lot, 1)
OVER (PARTITION BY product_code
    ORDER BY time_order),0) AS demand_lot,
FROM 
(
    SELECT
    *,
    #過去からのトータル要求数
    IFNULL(
        SUM(demand_pcs )
        OVER (PARTITION BY product_code
        ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) AS total_demand_pcs,
    #トータル要求数に対する必要なロット数
    CEIL(
        IFNULL(
            SUM(demand_pcs )
            OVER (PARTITION BY product_code
            ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0)
            / lot_pcs) AS total_demand_lot
    FROM
    project.dataset.lot_problem_sample
)
ORDER BY
product_code,
time_order

このSQLを実行すると、以下のような結果が得られる。

product	lot_pcs	time	demand	total	total_lot	surplus	demand_lot
a	10	1	11	11	2	9	2
a	10	2	12	23	3	7	1
a	10	3	5	28	3	2	0
a	10	4	0	28	3	2	0
a	10	5	6	34	4	6	1
a	10	6	10	44	5	6	1
a	10	7	8	52	6	8	1
a	10	8	9	61	7	9	1
a	10	9	9	70	7	0	0
a	10	10	9	79	8	1	1