DB

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

2021-04-21

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

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

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

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


必要な個数をロットの単位数量で割り算して余りを繰り上げればよくない?
それじゃ常に余りが出て端数在庫が積み上がるよね
そうか。そしたらどうすればいいんだろう。


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

この記事を書いている人


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

記事を読むメリット

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


ロット単位問題の例

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

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

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

ロット管理 - Wikipedia

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

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


このロット単位で製造するが、発注は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:1ロットの個数
  • time_order:時間の順
  • demand_pcs:生産要求のあった個数

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

a,10,2,12Aを次の日(2日目)に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

結果としては、1日目の11個要求に応える為に2ロット生産し、翌日は12個要求だが余りがあるので1ロットの製造で済ませている。



まとめ

今回はBigQueryでロット単位の必要数換算で前回の余りを考慮する方法について解説した。

ロット単位で生産して次回以降は生産した余りを考慮して生産するロット数を算出する。

必要な数から都度余りを引いてロット数を算出したくなるが、この方法では難しい。

やり方としては以下の通り。

必要ロット数計算の方法

  • 初回から現在までのトータルの必要数を算出
  • トータル必要数からトータルで必要なロット数を逆算
  • 今回までのトータルで必要なロット数から前回までのトータルを引けば、今回必要なロット数がわかる

  • 都度余りを計算するのではなく、これまでのトータルで考えるのがポイントですね



    他にもSQLのちょっとしたテクニックについてまとめているので、もし気になったら見てみて欲しい。

    ITipsと同じようなブログを作る方法

    ブログに興味がありますか?

    もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

    サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
    同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

    ランキング参加中

    にほんブログ村 IT技術ブログへ

    他にもブログやSNSで紹介してくれると励みになります。

    はてブのコメントで酷評されると泣きます(´;ω;`)

    -DB
    -,

    © 2024 ITips