2020年7月11日 星期六

促銷活動(買愈多抽愈多)

許多公司會進行促銷活動，以下將以先前書本訂購為例，每買一本將獲得一次抽奬機會，購買愈多機會愈大，符合資格者共12筆訂單共152本，將抽出20名，下表中第8筆李先生購買ASP.NET 開發實務(C#)應用共10本，則有10次抽獎機會。通常對於此類型需求常見會程式或CURSOR方式完成，在此說明如何以一組SQL即可完成。

Cust_Name
BOOK_NAME
SUM_QTY
1
張先生
AJAX 應用
22
2
張先生
Oracle管理實務
8
3
張先生
SQL Server 進階應用
7
4
曹先生
ASP.NET 開發實務(C#)
13
5
曹先生
C# 程式設計
4
6
曹先生
JAVA
10
7
李先生
AJAX 應用
23
8
李先生
ASP.NET 開發實務(C#)
10
9
李先生
Oracle管理實務
18
10
李先生
SQL Server 進階應用
11
11
陳先生
AJAX 應用
10
12
陳先生
Oracle管理實務
16
13
NULL
NULL
152

1.    將符合者訂購數量展開(152筆)，並取亂數
將銷售數量進行逐筆展開，如第8筆李先生購買ASP.NET 開發實務(C#)應用共10本，將有10次抽獎機會，對程式、T-SQL(MSSQL)或PL/SQL(ORACLE)會以迴圈方式逐筆取亂數值(抽獎)，對於SQL指令則為一項挑戰，再此可利笛卡兒積（Cartesian product）概念達成，可參考《九九乘法表-內建數值序列(Tally)功能之使用》。
另一項重點在於亂數取得，由於需取多組亂數，MSSQL雖有提供RAND函數，但因亂數種子(seed)不變，所產生的亂數均相同，因此需採用NEWID運算作法；ORACLE提供亂數套件可供使用，相當簡便，如亂數種子(seed)不變，則產生結果相同，可參考《亂數(Random)》文章所探討亂數使用。
MSSQL
ORACLE
SELECT O.ORDER_DATE
   , O.CUST_NAME
   , O.BOOK_NAME
   , O.QTY
   , N.number IDX
   , CAST(CHECKSUM(NEWID()) & 0x7fffffff AS float)
     / CAST (0x7fffffff AS bigint) VAL--2.亂數
FROM Orders O
    INNER JOIN master.dbo.spt_values N--1.a展開
        ON N.type ='P'
        AND O.Qty > N.number--1.b.重點
SELECT O.ORDER_DATE
  , O.CUST_NAME
 , O.BOOK_NAME
 , O.QTY
 , N.IDX
 , DBMS_RANDOM.VALUE VAL--2.亂數
FROM Orders O
     INNER JOIN 
     (--1.a.展開
     SELECT LEVEL Idx
     FROM DUAL
     CONNECT BY LEVEL<1000
     ) N
     ON O.Qty >= N.Idx--1.b.重點

利用笛卡兒積（Cartesian product）即可將銷售數量逐筆展開成共152筆資料，由於單筆銷售數量不會超過1000，因此ORACLE動態產生Tally數值序列時以999為上限值(仍可擴增)，MSSQL使用系統資料表，最大上限為2048，如訂購數量超上限值，則仍可使用《九九乘法表-內建數值序列(Tally)功能之使用》概念再擴增，不過應思考效能影響問題及行銷方案合理性，產生結果如下。
ORDER_DATE
CUST_NAME
BOOK_NAME
QTY
IDX
VAL
1
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
0
0.38708866
2
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
1
0.53386738
3
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
2
0.95699552
4
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
3
0.24549531
5
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
4
0.19382584
6
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
5
0.85724839
7
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
6
0.64801620
8
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
7
0.70403194
9
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
8
0.90705431
10
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
9
0.26450511
11
2009/8/1 09:30
張先生
SQL Server 進階應用
7
0
0.66450714
12
2009/8/1 09:30
張先生
SQL Server 進階應用
7
1
0.23021653







150
2009/8/3 16:15
張先生
Oracle管理實務
8
5
0.75513738
151
2009/8/3 16:15
張先生
Oracle管理實務
8
6
0.83927587
152
2009/8/3 16:15
張先生
Oracle管理實務
8
7
32271978

2.    將亂數值排序
將數值值以ROW_NUMBER取得其排名，將亂數值以反向排序取得其流水序號，可參考《次序函數》文章所述。

3.    取出前20名者
由於需留下相關佐證/軌跡，需將所有資料留存備查，將全數數保留僅以SEL欄位表示抽中與否，而對於ORACLE可設定亂數種子(Seed)，當亂數種子不變則可產生相同結果，如僅需取得20筆資料，則再以子查詢方式包夾後進行剔除即可。
SQL
MSSQL
SELECT O.ORDER_DATE
     , O.CUST_NAME
     , O.BOOK_NAME
     , O.QTY
     , O.IDX
     , O.VAL
        --2. 排名
     , ROW_NUMBER() OVER(ORDER BY VAL DESC) SEQ
        --3. 取得前20名
     , CASE WHEN ROW_NUMBER() OVER(ORDER BY VAL DESC) <=20
            THEN 'Y' ELSE 'N' END SEL
FROM
    (
       --1. 展開
    SELECT O.ORDER_DATE
         , O.CUST_NAME
         , O.BOOK_NAME
         , O.QTY
         , N.number IDX
         , CAST(CHECKSUM(NEWID()) & 0x7fffffff AS float)
           / CAST (0x7fffffff AS bigint) VAL--1.2.亂數
    FROM Orders O
        INNER JOIN master.dbo.spt_values N  --1.1.展開
            ON N.type ='P'
            AND O.Qty > N.number
    ) O
WHERE 1=1
         -- AND SEQ <=20 --如僅需保留20名，則要額外包來一層。
ORACLE
--exec dbms_random.seed(0);  --指定亂數種子(Seed)
SELECT O.ORDER_DATE
     , O.CUST_NAME
     , O.BOOK_NAME
     , O.QTY
     , O.IDX
     , O.VAL
     --2.取得序號
     , ROW_NUMBER() OVER(ORDER BY VAL DESC) SEQ
     --3.取得前20名
     , CASE WHEN ROW_NUMBER() OVER(ORDER BY VAL DESC) <= 20
            THEN 'Y' ELSE 'N' END SEL
FROM
   (
    --1. 依銷售數量展開並產生亂數
    SELECT O.ORDER_DATE
      , O.CUST_NAME
      , O.BOOK_NAME
      , O.QTY
      , N.IDX
      , DBMS_RANDOM.VALUE VAL--1.2.亂數
    FROM Orders O
         INNER JOIN 
         (--1.1.a.展開
         SELECT LEVEL Idx
         FROM DUAL
         CONNECT BY LEVEL<1000
         ) N
         ON O.Qty >= N.Idx--1.1.b.重點
  )  O

以下將列出前20名出處及中獎次數，SQL及結果如下。(亂數產生重新執行時結果會不同)。
ORDER_DATE
CUST_NAME
BOOK_NAME
QTY
WIN
1
2009/8/1 08:20
李先生
ASP.NET 開發實務(C#)
10
2
2
2009/8/1 09:30
張先生
SQL Server 進階應用
7
1
3
2009/8/1 10:50
陳先生
Oracle管理實務
16
2
4
2009/8/2 09:18
曹先生
C# 程式設計
4
1
5
2009/8/2 14:10
曹先生
JAVA
10
2
6
2009/8/2 16:50
李先生
AJAX 應用
15
3
7
2009/8/3 09:25
張先生
AJAX 應用
10
1
8
2009/8/3 09:33
李先生
SQL Server 進階應用
5
1
9
2009/8/3 11:47
李先生
Oracle管理實務
18
3
10
2009/8/3 13:39
曹先生
ASP.NET 開發實務(C#)
13
3
11
2009/8/3 15:51
張先生
AJAX 應用
12
1
12
(null)
(null)
(null)
(null)
20

SELECT TO_CHAR(ORDER_DATE, 'yyyy-mm-dd hh24:mi') ORDER_DATE
     , CUST_NAME
     , BOOK_NAME
     , QTY
     , COUNT(*) Win
FROM
    (
    SELECT O.ORDER_DATE
         , O.CUST_NAME
         , O.BOOK_NAME
         , O.QTY
         , O.IDX
         , O.VAL
         --2.取得序號
         , ROW_NUMBER() OVER(ORDER BY VAL DESC) SEQ
    FROM
       (
        --1. 依銷售數量展開並產生亂數
        SELECT O.ORDER_DATE
          , O.CUST_NAME
          , O.BOOK_NAME
          , O.QTY
          , N.IDX
          , DBMS_RANDOM.VALUE VAL--1.2.亂數
        FROM Orders O
             INNER JOIN 
             (--1.1.a.展開
             SELECT LEVEL Idx
             FROM DUAL
             CONNECT BY LEVEL<1000
             ) N
             ON O.Qty >= N.Idx--1.1.b.重點
      )  O
    )
WHERE 1=1
      AND SEQ<=20
GROUP BY GROUPING SETS(
       (ORDER_DATE
       , CUST_NAME
       , BOOK_NAME
       , QTY)
      , ())

參考資料:
1.       https://oracle-base.com/articles/misc/dbms_random
2.       https://docs.oracle.com/database/121/TTPLP/d_random.htm#TTPLP040

延伸閱讀文章：
#
延伸閱讀
1
亂數(Random)
2
九九乘法表-內建數值序列(Tally)功能之使用
3
次序函數


張貼者： 於 沒有留言:
訂閱： 文章 (Atom)