問題描述

• 電商場景中，優惠、運費等是以訂單為單位進行計算的
• 若要以商品維度進行分析，就要把優惠、運費等的效果分攤到每個商品
• 分攤方法：
  分 攤 比 重 = 每 種 商 品 的 金 額 商 品 總 額 分攤比重=\frac{每種商品的金額}{商品總額} 分攤比重=商品總額每種商品的金額?
• 問題來了，由于除法有時除不盡，導致合計有差
• 改進方法：
  最 后 一 種 商 品 分 攤 比 重 = 1 ? 其 它 分 攤 比 重 累 加 最后一種商品分攤比重 = 1 - 其它分攤比重累加 最后一種商品分攤比重=1?其它分攤比重累加
• 判斷是否最后一種商品：
  商 品 總 額 ? 已 經 分 攤 的 商 品 金 額 累 加 = = 商 品 金 額 商品總額 - 已經分攤的商品金額累加 == 商品金額 商品總額?已經分攤的商品金額累加==商品金額

Python實作

運費分攤

data = [
    ('a1', ['牛奶', '堅果', '蛋糕'], [30, 30, 30], 10, 100),
    ('a2', ['酸奶', '堅果', '蛋糕'], [60, 60, 60], 20, 200),
]

for 訂單號, 訂單商品, 訂單商品金額, 運費, 訂單總額 in data:
    商品總額 = 訂單總額 - 運費
    已經分攤的商品金額累加 = 0
    運費分攤累加 = 0
    # 遍歷每個商品，計算【運費分攤】
    for 商品, 商品金額 in zip(訂單商品, 訂單商品金額):
        # 判斷是否為最后一個商品
        if 商品總額 - 已經分攤的商品金額累加 == 商品金額:
            運費分攤 = 運費 - 運費分攤累加
        else:
            運費分攤 = round(商品金額 / 商品總額 * 運費, 2)
            已經分攤的商品金額累加 += 商品金額
            運費分攤累加 += 運費分攤
        print(訂單號, '\033[033m', 商品, 商品金額, 運費分攤, '\033[0m', 運費, 訂單總額)

運費和優惠分攤

data = [
    ('a1', ['牛奶', '堅果', '蛋糕'], [30, 30, 30], 10, 20, 80),
    ('a2', ['酸奶', '堅果', '蛋糕'], [60, 60, 60], 20, 40, 160),
]

for 訂單號, 訂單商品, 訂單商品金額, 運費, 優惠, 訂單總額 in data:
    商品總額 = 訂單總額 - 運費 + 優惠
    已經分攤的商品金額累加 = 0
    運費分攤累加 = 0
    優惠分攤累加 = 0
    # 遍歷每個商品，計算【運費分攤】
    for 商品, 商品金額 in zip(訂單商品, 訂單商品金額):
        # 判斷是否為最后一個商品
        if 商品總額 - 已經分攤的商品金額累加 == 商品金額:
            運費分攤 = 運費 - 運費分攤累加
            優惠分攤 = 優惠 - 優惠分攤累加
        else:
            已經分攤的商品金額累加 += 商品金額
            運費分攤 = round(商品金額 / 商品總額 * 運費, 2)
            運費分攤累加 += 運費分攤
            優惠分攤 = round(商品金額 / 商品總額 * 優惠, 2)
            優惠分攤累加 += 優惠分攤
        print(訂單號, '\033[033m', 商品, 商品金額, 運費分攤, 優惠分攤, '\033[0m', 運費, 優惠, 訂單總額)

列印

HIVE實作

--刪庫
DROP DATABASE IF EXISTS sale CASCADE;
--建庫
CREATE DATABASE sale LOCATION '/sale';
USE sale;
--建表
CREATE TABLE good(
order_number    STRING COMMENT "訂單號",
good            STRING COMMENT "商品",
good_costs      INT    COMMENT "商品金額",
goods_costs     INT    COMMENT "商品總額",
transport_costs INT    COMMENT "運費",
costs           INT    COMMENT "訂單總額"
)COMMENT "商品分析"
LOCATION '/sale/good';
--插資料
INSERT INTO TABLE good VALUES
('a1','牛奶',3000,9000,1000,10000),
('a1','堅果',3000,9000,1000,10000),
('a1','蛋糕',3000,9000,1000,10000),
('a2','酸奶',6000,18000,2000,20000),
('a2','堅果',6000,18000,2000,20000),
('a2','蛋糕',6000,18000,2000,20000);
--查看
SELECT * FROM good;

good1

CREATE VIEW good1 AS
SELECT
    *,
    SUM(good_costs) OVER(PARTITION BY order_number ORDER BY good)
        AS good_accumulation,  -- 累計商品金額
    ROUND(good_costs/goods_costs*transport_costs)
        AS transport_share     -- 分攤運費
FROM good;

good2

CREATE VIEW good2 AS
SELECT
    *,
    SUM(transport_share) OVER(PARTITION BY order_number ORDER BY good)
        AS transport_share_accumulation  -- 累計分攤運費
FROM good1;

good3

CREATE VIEW good3 AS
SELECT
    *,
    IF(goods_costs==good_accumulation,transport_costs-transport_share_accumulation+transport_share,transport_share)
        AS transport_share_final         -- 最終分攤運費
FROM good2;

123合體

SELECT
    *,
    IF(
        goods_costs==good_accumulation,
        transport_costs-transport_share_accumulation+transport_share,
        transport_share)
        AS transport_share_final         -- 最終分攤運費
FROM (
    SELECT
        *,
        SUM(good_costs) OVER(PARTITION BY order_number ORDER BY good)
            AS good_accumulation,  -- 累計商品金額
        ROUND(good_costs/goods_costs*transport_costs)
            AS transport_share,    -- 分攤運費
        SUM(ROUND(good_costs/goods_costs*transport_costs))
            OVER(PARTITION BY order_number ORDER BY good)
            AS transport_share_accumulation  -- 累計分攤運費
    FROM good
)t;

Spark實作

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object Hello {
  def main(args: Array[String]): Unit = {
    // 創建SparkSession物件
    val c1: SparkConf = new SparkConf().setAppName("a1").setMaster("local[*]")
    val spark: SparkSession = SparkSession.builder().config(c1).getOrCreate()
    // 隱式轉換支持
    import spark.implicits._
    // 創建資料
    val df = Seq(
      ("a1", "牛奶", 3000, 9000, 1000, 10000),
      ("a1", "堅果", 3000, 9000, 1000, 10000),
      ("a1", "蛋糕", 3000, 9000, 1000, 10000),
      ("a2", "酸奶", 6000, 18000, 2000, 20000),
      ("a2", "堅果", 6000, 18000, 2000, 20000),
      ("a2", "蛋糕", 6000, 18000, 2000, 20000)
    ).toDF("order_number", "good", "good_costs", "goods_costs", "transport_costs", "costs"
    ).sort("order_number")
    // 創建視圖
    df.createTempView("good")
    // 累計商品金額、分攤運費、累計分攤運費
    spark.sql(
      """
        |SELECT
        |    *,
        |    SUM(good_costs) OVER(PARTITION BY order_number ORDER BY good)
        |        AS good_accumulation,  -- 累計商品金額
        |    ROUND(good_costs/goods_costs*transport_costs)
        |        AS transport_share,    -- 分攤運費
        |    SUM(ROUND(good_costs/goods_costs*transport_costs))
        |        OVER(PARTITION BY order_number ORDER BY good)
        |        AS transport_share_accumulation  -- 累計分攤運費
        |FROM good
        |""".stripMargin).createTempView("good1")
    // 最終分攤運費
    spark.sql(
      """
        |SELECT
        |    *,
        |    IF(
        |        goods_costs==good_accumulation,
        |        transport_costs-transport_share_accumulation+transport_share,
        |        transport_share)
        |        AS transport_share_final         -- 最終分攤運費
        |FROM good1;
        |""".stripMargin).show()
  }
}

結果列印