• 某电商网站的数据库设计（6）——创建查询销售数据的视图

    某电商网站的数据库设计（6）——创建查询销售数据的视图

    目录

    创建视图查询店铺、平台、单品、小组、大组、品牌等的销售数据，所用到的视图如下：

    -- ============================================================
-- 各类统计信息视图（单个分组条件）
-- 1、统计总的销售信息
--    视图名：v_sales_all
--    说明：统计总的销售信息

-- 2、按日期分组统计
--    视图名：v_sales_date
--    说明：统计每天的销售信息

-- 3、按店铺分组统计
--    视图名：v_sales_store
--    说明：统计每个店铺每天的销售信息

-- 4、按销售平台分组统计
--    视图名：v_sales_platform
--    说明：统计每个销售平台每天的销售信息

-- 5、按单品分组统计
--    视图名：v_sales_product
--    说明：统计每个单品每天的销售信息

-- 6、按小组分组统计
--    视图名：v_sales_team
--    说明：统计每个小组每天的销售信息

-- 7、按大组分组统计
--    视图名：v_sales_large_group
--    说明：统计每个大组每天的销售信息

-- 8、按品牌分组统计
--    视图名：v_sales_brand
--    说明：统计每个品牌每天的销售信息
-- ============================================================

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    1、统计总的销售信息

    创建视图的SQL语句如下：

    -- 1、统计总的销售信息
--    视图名：v_sales_all
--    说明：统计总的销售信息
-- 视图包含：买家数量（buyers）
--           支付金额（pay）
--           退款金额（refund）
--           刷单数量（brush_order）
--           刷单金额（brush_amount）
--           佣金（commission）
--           转化率（conversion_rate）：买家数量/访客数量


DROP VIEW IF EXISTS v_sales_all;

CREATE VIEW v_sales_all
AS
SELECT 
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    查询视图数据：

    /*
-- 数据查询语句
select '总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
from v_sales_all;
*/
mysql> select '总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
    -> from v_sales_all;
+--------+--------------+--------------+-----------------+-----------------+--------------+-----------------+-----------+--------------+
| 总计   | 总访客数     | 总买家数     | 支付总金额      | 退款总金额      | 总刷单数     | 总刷单金额      | 总佣金    | 总转化率     |
+--------+--------------+--------------+-----------------+-----------------+--------------+-----------------+-----------+--------------+
| 总计   |        41745 |        13022 |      1317079.80 |       320177.14 |        11973 |       122036.81 |  41872.61 |       0.3119 |
+--------+--------------+--------------+-----------------+-----------------+--------------+-----------------+-----------+--------------+
1 row in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    2、按日期分组统计

    创建视图的SQL语句如下：

    -- 7、按日期分组统计
--    视图名：v_sales_date
--    说明：统计每天的销售信息
-- 视图包含：销售日期（sales_date）
--           访客数量（visitors）
--           买家数量（buyers）
--           支付金额（pay）
--           退款金额（refund）
--           刷单数量（brush_order）
--           刷单金额（brush_amount）
--           佣金（commission）
--           转化率（conversion_rate）：买家数量/访客数量


DROP VIEW IF EXISTS v_sales_date;

CREATE VIEW v_sales_date
AS
SELECT 
    v_sales.sales_date sales_date,
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales
GROUP BY
    sales_date
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    查询视图数据：

    /*
-- 数据查询语句
select * from v_sales_date
union
select '总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
from v_sales;
*/

mysql> select * from v_sales_date
    -> union
    -> select '总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
    -> from v_sales;
+---------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| sales_date          | visitors | buyers | pay        | refund    | brush_order | brush_amount | commission | conversion_rate |
+---------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| 2022-01-01 00:00:00 |    13477 |   4049 |  429998.33 | 108550.69 |        3985 |     40952.89 |   13386.40 |          0.3004 |
| 2022-01-02 00:00:00 |    14675 |   4690 |  463066.32 | 104977.22 |        4023 |     40825.91 |   14498.77 |          0.3196 |
| 2022-01-03 00:00:00 |    13593 |   4283 |  424015.15 | 106649.23 |        3965 |     40258.01 |   13987.44 |          0.3151 |
| 总计                |    41745 |  13022 | 1317079.80 | 320177.14 |       11973 |    122036.81 |   41872.61 |          0.3119 |
+---------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
4 rows in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    3、按店铺分组统计——全店汇总

    创建视图的SQL语句如下：

    -- 1、按店铺分组统计
-- 视图名：v_sales_store
-- 说明：统计每个店铺每天的销售信息
-- 视图包含：销售日期（sales_date）
--           店铺编号（store_no）
--           店铺名称（store_name）
--           访客数量（visitors）
--           买家数量（buyers）
--           支付金额（pay）
--           退款金额（refund）
--           刷单数量（brush_order）
--           刷单金额（brush_amount）
--           佣金（commission）
--           转化率（conversion_rate）：买家数量/访客数量


DROP VIEW IF EXISTS v_sales_store;

CREATE VIEW v_sales_store
AS
SELECT 
    v_sales.sales_date sales_date,
    tb_store.store_no store_no,
    tb_store.store_name store_name,
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales INNER JOIN tb_store
    on v_sales.store_no = tb_store.store_no
GROUP BY
    sales_date,
    store_no,
    store_name
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    查询视图数据：

    /*
-- 数据查询语句
select * from v_sales_store
union
select '','','总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
from v_sales_store;
*/

mysql> select * from v_sales_store
    -> union
    -> select '','','总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
    -> from v_sales_store;
+---------------------+-------------+-----------------------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| sales_date          | store_no    | store_name                        | visitors | buyers | pay        | refund    | brush_order | brush_amount | commission | conversion_rate |
+---------------------+-------------+-----------------------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| 2022-01-01 00:00:00 | DERDN-JD001 | 戴尔电脑京东旗舰店                |      697 |    213 |   27519.56 |   6688.04 |         232 |      2604.82 |     647.45 |          0.3056 |
| 2022-01-01 00:00:00 | DERDN-TM001 | 戴尔电脑天猫旗舰店                |      571 |    180 |   30082.40 |   5857.42 |         213 |      1826.58 |     710.71 |          0.3152 |
| 2022-01-01 00:00:00 | FZDN-JD001  | 方正电脑京东旗舰店                |      761 |    203 |   26818.01 |   5277.98 |         258 |      1744.21 |     891.84 |          0.2668 |
| 2022-01-01 00:00:00 | FZDN-TM001  | 方正电脑天猫旗舰店                |      845 |    274 |   22806.63 |   8611.37 |         219 |      3487.13 |     976.03 |          0.3243 |
| 2022-01-01 00:00:00 | HPDN-JD001  | 惠普电脑京东旗舰店                |      911 |    251 |   26720.68 |   5649.48 |         256 |      1827.37 |     806.65 |          0.2755 |
| 2022-01-01 00:00:00 | HPDN-TM001  | 惠普电脑天猫旗舰店                |      634 |    252 |   25755.53 |   6735.82 |         273 |      2969.84 |     981.65 |          0.3975 |
| 2022-01-01 00:00:00 | JC-JD001    | 京瓷办公用品京东旗舰店            |      807 |    186 |   21702.70 |   4583.24 |         239 |      2719.84 |     584.39 |          0.2305 |
| 2022-01-01 00:00:00 | JC-TM001    | 京瓷办公用品天猫旗舰店            |     1113 |    374 |   32215.81 |   9109.17 |         238 |      2990.00 |     680.51 |          0.3360 |
| 2022-01-01 00:00:00 | LX-JD001    | 联想办公用品京东旗舰店            |      664 |    350 |   23126.32 |   8344.95 |         261 |      1558.50 |    1105.32 |          0.5271 |
| 2022-01-01 00:00:00 | LX-TM001    | 联想办公用品天猫旗舰店            |      759 |    141 |   27152.82 |   6396.27 |         260 |      2188.73 |    1290.08 |          0.1858 |
| 2022-01-01 00:00:00 | SZDN-JD001  | 神州电脑京东旗舰店                |      681 |    324 |   21460.44 |   5406.36 |         238 |      2901.82 |     674.50 |          0.4758 |
| 2022-01-01 00:00:00 | SZDN-TM001  | 神州电脑天猫旗舰店                |      811 |    210 |   33060.23 |   6560.90 |         280 |      1994.40 |     719.88 |          0.2589 |
| 2022-01-01 00:00:00 | XD-JD001    | 兄弟办公用品京东旗舰店            |      688 |    180 |   21546.57 |   5716.73 |         222 |      2605.93 |     711.91 |          0.2616 |
| 2022-01-01 00:00:00 | XD-TM001    | 兄弟办公用品天猫旗舰店            |      797 |    246 |   27388.41 |   6907.72 |         240 |      2930.44 |     776.49 |          0.3087 |
| 2022-01-01 00:00:00 | XP-JD001    | 夏普办公用品京东旗舰店            |      667 |    322 |   26928.56 |   8514.85 |         261 |      3259.20 |     930.07 |          0.4828 |
| 2022-01-01 00:00:00 | XP-TM001    | 夏普办公用品天猫旗舰店            |     2071 |    343 |   35713.66 |   8190.39 |         295 |      3344.08 |     898.92 |          0.1656 |
| 2022-01-02 00:00:00 | DERDN-JD001 | 戴尔电脑京东旗舰店                |      874 |    263 |   21116.87 |   7015.95 |         275 |      2605.00 |     902.77 |          0.3009 |
| 2022-01-02 00:00:00 | DERDN-TM001 | 戴尔电脑天猫旗舰店                |      824 |    307 |   34093.90 |   7563.07 |         265 |      1338.40 |     439.27 |          0.3726 |
| 2022-01-02 00:00:00 | FZDN-JD001  | 方正电脑京东旗舰店                |      716 |    224 |   17851.94 |   5776.53 |         304 |      2394.15 |     949.58 |          0.3128 |
| 2022-01-02 00:00:00 | FZDN-TM001  | 方正电脑天猫旗舰店                |      896 |    257 |   26942.76 |   6943.79 |         245 |      2788.62 |     881.16 |          0.2868 |
| 2022-01-02 00:00:00 | HPDN-JD001  | 惠普电脑京东旗舰店                |      770 |    280 |   25282.34 |   6570.39 |         241 |      2584.84 |     721.72 |          0.3636 |
| 2022-01-02 00:00:00 | HPDN-TM001  | 惠普电脑天猫旗舰店                |      575 |    269 |   24315.66 |   3923.43 |         235 |      1928.36 |     840.81 |          0.4678 |
| 2022-01-02 00:00:00 | JC-JD001    | 京瓷办公用品京东旗舰店            |      832 |    326 |   31244.11 |   4625.26 |         233 |      2724.77 |     827.51 |          0.3918 |
| 2022-01-02 00:00:00 | JC-TM001    | 京瓷办公用品天猫旗舰店            |     1198 |    375 |   60006.73 |   7885.10 |         259 |      4150.87 |    1103.15 |          0.3130 |
| 2022-01-02 00:00:00 | LX-JD001    | 联想办公用品京东旗舰店            |      851 |    331 |   28561.98 |   6628.57 |         267 |      2062.20 |     678.99 |          0.3890 |
| 2022-01-02 00:00:00 | LX-TM001    | 联想办公用品天猫旗舰店            |      712 |    283 |   37673.34 |   6753.44 |         310 |      3010.32 |    1033.50 |          0.3975 |
| 2022-01-02 00:00:00 | SZDN-JD001  | 神州电脑京东旗舰店                |      644 |    212 |   24724.24 |   7637.03 |         210 |      3050.36 |    1129.49 |          0.3292 |
| 2022-01-02 00:00:00 | SZDN-TM001  | 神州电脑天猫旗舰店                |      617 |    283 |   18694.41 |   6641.89 |         242 |      2539.47 |    1183.90 |          0.4587 |
| 2022-01-02 00:00:00 | XD-JD001    | 兄弟办公用品京东旗舰店            |      852 |    293 |   26590.28 |   7498.28 |         236 |      2537.69 |     842.71 |          0.3439 |
| 2022-01-02 00:00:00 | XD-TM001    | 兄弟办公用品天猫旗舰店            |      691 |    320 |   26803.71 |   6802.19 |         213 |      1876.97 |     550.63 |          0.4631 |
| 2022-01-02 00:00:00 | XP-JD001    | 夏普办公用品京东旗舰店            |      664 |    239 |   29285.12 |   6708.04 |         227 |      1567.07 |    1391.51 |          0.3599 |
| 2022-01-02 00:00:00 | XP-TM001    | 夏普办公用品天猫旗舰店            |     2959 |    428 |   29878.93 |   6004.26 |         261 |      3666.82 |    1022.07 |          0.1446 |
| 2022-01-03 00:00:00 | DERDN-JD001 | 戴尔电脑京东旗舰店                |      752 |    287 |   18603.18 |   7092.69 |         262 |      2672.66 |     860.80 |          0.3816 |
| 2022-01-03 00:00:00 | DERDN-TM001 | 戴尔电脑天猫旗舰店                |      815 |    281 |   26263.08 |   7122.86 |         230 |      3078.71 |     964.29 |          0.3448 |
| 2022-01-03 00:00:00 | FZDN-JD001  | 方正电脑京东旗舰店                |      909 |    281 |   27885.34 |   7258.70 |         232 |      2103.23 |     804.53 |          0.3091 |
| 2022-01-03 00:00:00 | FZDN-TM001  | 方正电脑天猫旗舰店                |      815 |    250 |   24438.31 |   5399.25 |         204 |      2494.04 |    1010.53 |          0.3067 |
| 2022-01-03 00:00:00 | HPDN-JD001  | 惠普电脑京东旗舰店                |      741 |    184 |   28899.63 |   7175.11 |         268 |      2707.73 |     612.12 |          0.2483 |
| 2022-01-03 00:00:00 | HPDN-TM001  | 惠普电脑天猫旗舰店                |      513 |    228 |   14124.18 |   5293.34 |         267 |      2128.49 |    1326.34 |          0.4444 |
| 2022-01-03 00:00:00 | JC-JD001    | 京瓷办公用品京东旗舰店            |      713 |    205 |   31527.92 |   5203.49 |         245 |      1932.17 |     833.86 |          0.2875 |
| 2022-01-03 00:00:00 | JC-TM001    | 京瓷办公用品天猫旗舰店            |     2119 |    259 |   38794.56 |   6110.72 |         238 |      4077.25 |    1053.79 |          0.1222 |
| 2022-01-03 00:00:00 | LX-JD001    | 联想办公用品京东旗舰店            |      659 |    354 |   24885.86 |   5458.75 |         260 |      1721.80 |     708.45 |          0.5372 |
| 2022-01-03 00:00:00 | LX-TM001    | 联想办公用品天猫旗舰店            |      861 |    293 |   20232.57 |   6967.05 |         236 |      3013.79 |     838.13 |          0.3403 |
| 2022-01-03 00:00:00 | SZDN-JD001  | 神州电脑京东旗舰店                |      713 |    309 |   24542.32 |   7084.30 |         242 |      2137.30 |     525.89 |          0.4334 |
| 2022-01-03 00:00:00 | SZDN-TM001  | 神州电脑天猫旗舰店                |      688 |    194 |   13442.51 |   9370.73 |         280 |      1844.08 |     522.31 |          0.2820 |
| 2022-01-03 00:00:00 | XD-JD001    | 兄弟办公用品京东旗舰店            |      567 |    355 |   31671.81 |   7668.54 |         312 |      1217.08 |    1075.64 |          0.6261 |
| 2022-01-03 00:00:00 | XD-TM001    | 兄弟办公用品天猫旗舰店            |      900 |    235 |   33380.35 |   5899.07 |         220 |      2553.57 |     998.89 |          0.2611 |
| 2022-01-03 00:00:00 | XP-JD001    | 夏普办公用品京东旗舰店            |      584 |    223 |   29328.39 |   7230.40 |         242 |      2529.14 |    1437.51 |          0.3818 |
| 2022-01-03 00:00:00 | XP-TM001    | 夏普办公用品天猫旗舰店            |     1244 |    345 |   35995.14 |   6314.23 |         227 |      4046.97 |     414.36 |          0.2773 |
|                     |             | 总计                              |    41745 |  13022 | 1317079.80 | 320177.14 |       11973 |    122036.81 |   41872.61 |          0.3119 |
+---------------------+-------------+-----------------------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
49 rows in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    4、按销售平台分组统计

    创建视图的SQL语句如下：

    -- 2、按销售平台分组统计
-- 视图名：v_sales_platform
-- 说明：统计每个销售平台每天的销售信息
-- 视图包含：销售日期（sales_date）
--           店铺编号（store_no）
--           店铺名称（store_name）
--           访客数量（visitors）
--           买家数量（buyers）
--           支付金额（pay）
--           退款金额（refund）
--           刷单数量（brush_order）
--           刷单金额（brush_amount）
--           佣金（commission）
--           转化率（conversion_rate）：买家数量/访客数量


DROP VIEW IF EXISTS v_sales_platform;

CREATE VIEW v_sales_platform
AS
SELECT 
    v_sales.sales_date sales_date,
    v_relation_store_platform.platform_no platform_no,
    v_relation_store_platform.platform_name platform_name,
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales INNER JOIN v_relation_store_platform
    on v_sales.store_no = v_relation_store_platform.store_no
GROUP BY
    sales_date,
    platform_no,
    platform_name
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    查询视图数据：

    /*
-- 数据查询语句
select * from v_sales_platform
union
select '','','总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
from v_sales_store;
*/

mysql> select * from v_sales_platform
    -> union
    -> select '','','总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
    -> from v_sales_store;
+---------------------+-------------+---------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| sales_date          | platform_no | platform_name | visitors | buyers | pay        | refund    | brush_order | brush_amount | commission | conversion_rate |
+---------------------+-------------+---------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| 2022-01-01 00:00:00 | JD          | 京东          |     5876 |   2029 |  195822.84 |  50181.63 |        1967 |     19221.69 |    6352.13 |          0.3453 |
| 2022-01-01 00:00:00 | TM          | 天猫          |     7601 |   2020 |  234175.49 |  58369.06 |        2018 |     21731.20 |    7034.27 |          0.2658 |
| 2022-01-02 00:00:00 | JD          | 京东          |     6203 |   2168 |  204656.88 |  52460.05 |        1993 |     19526.08 |    7444.28 |          0.3495 |
| 2022-01-02 00:00:00 | TM          | 天猫          |     8472 |   2522 |  258409.44 |  52517.17 |        2030 |     21299.83 |    7054.49 |          0.2977 |
| 2022-01-03 00:00:00 | JD          | 京东          |     5638 |   2198 |  217344.45 |  54171.98 |        2063 |     17021.11 |    6858.80 |          0.3899 |
| 2022-01-03 00:00:00 | TM          | 天猫          |     7955 |   2085 |  206670.70 |  52477.25 |        1902 |     23236.90 |    7128.64 |          0.2621 |
|                     |             | 总计          |    41745 |  13022 | 1317079.80 | 320177.14 |       11973 |    122036.81 |   41872.61 |          0.3119 |
+---------------------+-------------+---------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
7 rows in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    5、按单品分组统计

    创建视图的SQL语句如下：

    -- 3、按单品分组统计
-- 视图名：v_sales_product
-- 说明：统计每个单品每天的销售信息
-- 视图包含：销售日期（sales_date）
--           单品编号（product_no）
--           单品名称（product_name）
--           访客数量（visitors）
--           买家数量（buyers）
--           支付金额（pay）
--           退款金额（refund）
--           刷单数量（brush_order）
--           刷单金额（brush_amount）
--           佣金（commission）
--           转化率（conversion_rate）：买家数量/访客数量


DROP VIEW IF EXISTS v_sales_product;

CREATE VIEW v_sales_product
AS
SELECT 
    v_sales.sales_date sales_date,
    v_sales.product_no product_no,
    tb_product.product_name product_name,
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales INNER JOIN tb_product
    on v_sales.product_no = tb_product.product_no
GROUP BY
    sales_date,
    product_no,
    product_name
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    查询视图数据：

    /*
-- 数据查询语句
select * from v_sales_product
union
select '','','总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
from v_sales_store;
*/
mysql> select * from v_sales_product
    -> union
    -> select '','','总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
    -> from v_sales_store;
+---------------------+------------+--------------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| sales_date          | product_no | product_name             | visitors | buyers | pay        | refund    | brush_order | brush_amount | commission | conversion_rate |
+---------------------+------------+--------------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| 2022-01-01 00:00:00 | DER-NS-01  | 戴尔笔记本               |      908 |    293 |   35127.05 |   7754.98 |         308 |      2856.63 |     962.69 |          0.3227 |
| 2022-01-01 00:00:00 | DER-NS-02  | 戴尔平板                 |      360 |    100 |   22474.91 |   4790.48 |         137 |      1574.77 |     395.47 |          0.2778 |
| 2022-01-01 00:00:00 | FZ-NS-01   | 方正台式机               |     1091 |    348 |   32948.15 |   9538.52 |         289 |      3587.55 |    1426.80 |          0.3190 |
| 2022-01-01 00:00:00 | FZ-NS-02   | 方正一体机               |      515 |    129 |   16676.49 |   4350.83 |         188 |      1643.79 |     441.07 |          0.2505 |
| 2022-01-01 00:00:00 | HP-NS-04   | 惠普笔记本               |      499 |    177 |   11149.98 |   5046.92 |         187 |      1585.75 |     424.34 |          0.3547 |
| 2022-01-01 00:00:00 | HP-NS-05   | 惠普平板                 |     1046 |    326 |   41326.23 |   7338.38 |         342 |      3211.46 |    1363.96 |          0.3117 |
| 2022-01-01 00:00:00 | JC-SF-01   | 京瓷复印机               |     1216 |    341 |   33728.54 |   8164.56 |         297 |      4129.34 |    1065.43 |          0.2804 |
| 2022-01-01 00:00:00 | JC-SF-02   | 京瓷一体机               |      704 |    219 |   20189.97 |   5527.85 |         180 |      1580.50 |     199.47 |          0.3111 |
| 2022-01-01 00:00:00 | LX-HF-01   | 联想打印机               |      978 |    314 |   34067.08 |   9355.00 |         315 |      3047.97 |    1730.76 |          0.3211 |
| 2022-01-01 00:00:00 | LX-HF-02   | 联想三合一一体机         |      445 |    177 |   16212.06 |   5386.22 |         206 |       699.26 |     664.64 |          0.3978 |
| 2022-01-01 00:00:00 | SZ_HF-01   | 神州台式机               |      406 |    184 |   13813.02 |   5149.93 |         169 |      1344.26 |     585.76 |          0.4532 |
| 2022-01-01 00:00:00 | SZ_HF-02   | 神州一体机               |     1086 |    350 |   40707.65 |   6817.33 |         349 |      3551.96 |     808.62 |          0.3223 |
| 2022-01-01 00:00:00 | XD-HF-01   | 兄弟打印机               |     1045 |    303 |   27888.76 |   8896.10 |         311 |      3459.92 |     854.14 |          0.2900 |
| 2022-01-01 00:00:00 | XD-HF-02   | 兄弟五合一一体机         |      440 |    123 |   21046.22 |   3728.35 |         151 |      2076.45 |     634.26 |          0.2795 |
| 2022-01-01 00:00:00 | XP-SR-01   | 夏普复印机               |     1499 |    420 |   38820.81 |  10026.10 |         377 |      3671.07 |    1228.91 |          0.2802 |
| 2022-01-01 00:00:00 | XP-SR-02   | 夏普一体机               |     1239 |    245 |   23821.41 |   6679.14 |         179 |      2932.21 |     600.08 |          0.1977 |
| 2022-01-02 00:00:00 | DER-NS-01  | 戴尔笔记本               |     1127 |    451 |   33782.74 |   9502.52 |         354 |      2310.00 |    1083.58 |          0.4002 |
| 2022-01-02 00:00:00 | DER-NS-02  | 戴尔平板                 |      571 |    119 |   21428.03 |   5076.50 |         186 |      1633.40 |     258.46 |          0.2084 |
| 2022-01-02 00:00:00 | FZ-NS-01   | 方正台式机               |     1021 |    330 |   30070.33 |   7980.27 |         358 |      3137.16 |    1057.35 |          0.3232 |
| 2022-01-02 00:00:00 | FZ-NS-02   | 方正一体机               |      591 |    151 |   14724.37 |   4740.05 |         191 |      2045.61 |     773.39 |          0.2555 |
| 2022-01-02 00:00:00 | HP-NS-04   | 惠普笔记本               |      361 |    251 |   16074.64 |   2909.78 |         182 |      1884.91 |     552.16 |          0.6953 |
| 2022-01-02 00:00:00 | HP-NS-05   | 惠普平板                 |      984 |    298 |   33523.36 |   7584.04 |         294 |      2628.29 |    1010.37 |          0.3028 |
| 2022-01-02 00:00:00 | JC-SF-01   | 京瓷复印机               |     1450 |    478 |   59070.92 |   7922.08 |         335 |      4267.64 |    1423.67 |          0.3297 |
| 2022-01-02 00:00:00 | JC-SF-02   | 京瓷一体机               |      580 |    223 |   32179.92 |   4588.28 |         157 |      2608.00 |     506.99 |          0.3845 |
| 2022-01-02 00:00:00 | LX-HF-01   | 联想打印机               |     1050 |    454 |   44636.51 |   8696.27 |         384 |      3519.78 |    1456.74 |          0.4324 |
| 2022-01-02 00:00:00 | LX-HF-02   | 联想三合一一体机         |      513 |    160 |   21598.81 |   4685.74 |         193 |      1552.74 |     255.75 |          0.3119 |
| 2022-01-02 00:00:00 | SZ_HF-01   | 神州台式机               |      484 |    142 |   16202.36 |   6106.92 |         126 |      1732.11 |     574.51 |          0.2934 |
| 2022-01-02 00:00:00 | SZ_HF-02   | 神州一体机               |      777 |    353 |   27216.29 |   8172.00 |         326 |      3857.72 |    1738.88 |          0.4543 |
| 2022-01-02 00:00:00 | XD-HF-01   | 兄弟打印机               |     1062 |    442 |   38186.20 |   8860.59 |         317 |      2910.51 |    1089.91 |          0.4162 |
| 2022-01-02 00:00:00 | XD-HF-02   | 兄弟五合一一体机         |      481 |    171 |   15207.79 |   5439.88 |         132 |      1504.15 |     303.43 |          0.3555 |
| 2022-01-02 00:00:00 | XP-SR-01   | 夏普复印机               |     1868 |    456 |   37774.34 |   7342.14 |         318 |      3153.80 |    1923.16 |          0.2441 |
| 2022-01-02 00:00:00 | XP-SR-02   | 夏普一体机               |     1755 |    211 |   21389.71 |   5370.16 |         170 |      2080.09 |     490.42 |          0.1202 |
| 2022-01-03 00:00:00 | DER-NS-01  | 戴尔笔记本               |     1044 |    399 |   28785.16 |   8880.56 |         338 |      3922.00 |    1250.15 |          0.3822 |
| 2022-01-03 00:00:00 | DER-NS-02  | 戴尔平板                 |      523 |    169 |   16081.10 |   5334.99 |         154 |      1829.37 |     574.94 |          0.3231 |
| 2022-01-03 00:00:00 | FZ-NS-01   | 方正台式机               |     1069 |    312 |   38212.48 |   9533.30 |         289 |      3063.72 |     933.16 |          0.2919 |
| 2022-01-03 00:00:00 | FZ-NS-02   | 方正一体机               |      655 |    219 |   14111.17 |   3124.65 |         147 |      1533.55 |     881.90 |          0.3344 |
| 2022-01-03 00:00:00 | HP-NS-04   | 惠普笔记本               |      474 |    183 |   19387.43 |   4646.70 |         203 |      2527.28 |     611.14 |          0.3861 |
| 2022-01-03 00:00:00 | HP-NS-05   | 惠普平板                 |      780 |    229 |   23636.38 |   7821.75 |         332 |      2308.94 |    1327.32 |          0.2936 |
| 2022-01-03 00:00:00 | JC-SF-01   | 京瓷复印机               |     1875 |    305 |   44438.49 |   7115.02 |         338 |      3938.06 |    1295.66 |          0.1627 |
| 2022-01-03 00:00:00 | JC-SF-02   | 京瓷一体机               |      957 |    159 |   25883.99 |   4199.19 |         145 |      2071.36 |     591.99 |          0.1661 |
| 2022-01-03 00:00:00 | LX-HF-01   | 联想打印机               |      978 |    414 |   29014.46 |   8325.56 |         332 |      3269.85 |    1049.22 |          0.4233 |
| 2022-01-03 00:00:00 | LX-HF-02   | 联想三合一一体机         |      542 |    233 |   16103.97 |   4100.24 |         164 |      1465.74 |     497.36 |          0.4299 |
| 2022-01-03 00:00:00 | SZ_HF-01   | 神州台式机               |      585 |    149 |   12994.85 |   5942.72 |         179 |      1318.73 |     128.93 |          0.2547 |
| 2022-01-03 00:00:00 | SZ_HF-02   | 神州一体机               |      816 |    354 |   24989.98 |  10512.31 |         343 |      2662.65 |     919.27 |          0.4338 |
| 2022-01-03 00:00:00 | XD-HF-01   | 兄弟打印机               |     1024 |    385 |   46576.88 |   9551.56 |         354 |      2745.87 |    1254.67 |          0.3760 |
| 2022-01-03 00:00:00 | XD-HF-02   | 兄弟五合一一体机         |      443 |    205 |   18475.28 |   4016.05 |         178 |      1024.78 |     819.86 |          0.4628 |
| 2022-01-03 00:00:00 | XP-SR-01   | 夏普复印机               |     1143 |    390 |   39917.45 |   9883.19 |         325 |      4269.33 |    1235.65 |          0.3412 |
| 2022-01-03 00:00:00 | XP-SR-02   | 夏普一体机               |      685 |    178 |   25406.08 |   3661.44 |         144 |      2306.78 |     616.22 |          0.2599 |
|                     |            | 总计                     |    41745 |  13022 | 1317079.80 | 320177.14 |       11973 |    122036.81 |   41872.61 |          0.3119 |
+---------------------+------------+--------------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
49 rows in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    6、按小组分组统计

    创建视图的SQL语句如下：

    -- 4、按小组分组统计
-- 视图名：v_sales_team
-- 说明：统计每个小组每天的销售信息
-- 视图包含：销售日期（sales_date）
--           小组编号（team_no）
--           小组名称（team_name）
--           小组组长姓名（team_header）
--           访客数量（visitors）
--           买家数量（buyers）
--           支付金额（pay）
--           退款金额（refund）
--           刷单数量（brush_order）
--           刷单金额（brush_amount）
--           佣金（commission）
--           转化率（conversion_rate）：买家数量/访客数量



DROP VIEW IF EXISTS v_sales_team;

CREATE VIEW v_sales_team
AS
SELECT 
    v_sales.sales_date sales_date,
    v_sales.team_no team_no,
    tb_team.team_name team_name,
    tb_team.team_header team_header,
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales INNER JOIN tb_team
    on v_sales.team_no = tb_team.team_no
    INNER JOIN tb_product
    on v_sales.product_no = tb_product.product_no
GROUP BY
    sales_date,
    team_no,
    team_name,
    team_header
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    查询视图数据：

    /*
-- 数据查询语句
select * from v_sales_team
union
select '','','','总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
from v_sales_store;
*/

mysql> select * from v_sales_team
    -> union
    -> select '','','','总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
    -> from v_sales_store;
+---------------------+-----------+-----------------------+-------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| sales_date          | team_no   | team_name             | team_header | visitors | buyers | pay        | refund    | brush_order | brush_amount | commission | conversion_rate |
+---------------------+-----------+-----------------------+-------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| 2022-01-01 00:00:00 | DZ01-XZ01 | 台式机销售小组        | 诸葛亮      |     3098 |   1011 |  104145.31 |  25856.61 |         995 |     10127.56 |    3262.25 |          0.3263 |
| 2022-01-01 00:00:00 | DZ01-XZ02 | 笔记本销售小组        | 庞统        |     2813 |    896 |  110078.17 |  24930.76 |         974 |      9228.61 |    3146.46 |          0.3185 |
| 2022-01-01 00:00:00 | DZ02-XZ03 | 打印机销售小组        | 司马懿      |     2908 |    917 |   99214.12 |  27365.67 |         983 |      9283.60 |    3883.80 |          0.3153 |
| 2022-01-01 00:00:00 | DZ02-XZ04 | 复印机销售小组        | 徐庶        |     4658 |   1225 |  116560.73 |  30397.65 |        1033 |     12313.12 |    3093.89 |          0.2630 |
| 2022-01-02 00:00:00 | DZ01-XZ01 | 台式机销售小组        | 诸葛亮      |     2873 |    976 |   88213.35 |  26999.24 |        1001 |     10772.60 |    4144.13 |          0.3397 |
| 2022-01-02 00:00:00 | DZ01-XZ02 | 笔记本销售小组        | 庞统        |     3043 |   1119 |  104808.77 |  25072.84 |        1016 |      8456.60 |    2904.57 |          0.3677 |
| 2022-01-02 00:00:00 | DZ02-XZ03 | 打印机销售小组        | 司马懿      |     3106 |   1227 |  119629.31 |  27682.48 |        1026 |      9487.18 |    3105.83 |          0.3950 |
| 2022-01-02 00:00:00 | DZ02-XZ04 | 复印机销售小组        | 徐庶        |     5653 |   1368 |  150414.89 |  25222.66 |         980 |     12109.53 |    4344.24 |          0.2420 |
| 2022-01-03 00:00:00 | DZ01-XZ01 | 台式机销售小组        | 诸葛亮      |     3125 |   1034 |   90308.48 |  29112.98 |         958 |      8578.65 |    2863.26 |          0.3309 |
| 2022-01-03 00:00:00 | DZ01-XZ02 | 笔记本销售小组        | 庞统        |     2821 |    980 |   87890.07 |  26684.00 |        1027 |     10587.59 |    3763.55 |          0.3474 |
| 2022-01-03 00:00:00 | DZ02-XZ03 | 打印机销售小组        | 司马懿      |     2987 |   1237 |  110170.59 |  25993.41 |        1028 |      8506.24 |    3621.11 |          0.4141 |
| 2022-01-03 00:00:00 | DZ02-XZ04 | 复印机销售小组        | 徐庶        |     4660 |   1032 |  135646.01 |  24858.84 |         952 |     12585.53 |    3739.52 |          0.2215 |
|                     |           |                       | 总计        |    41745 |  13022 | 1317079.80 | 320177.14 |       11973 |    122036.81 |   41872.61 |          0.3119 |
+---------------------+-----------+-----------------------+-------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
13 rows in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    7、按大组分组统计

    创建视图的SQL语句如下：

    -- 5、按大组分组统计
-- 视图名：v_sales_large_group
-- 说明：统计每个大组每天的销售信息
-- 视图包含：销售日期（sales_date）
--           大组编号（lg_no）
--           大组名称（lg_name）
--           小组组长姓名（team_header）
--           访客数量（visitors）
--           买家数量（buyers）
--           支付金额（pay）
--           退款金额（refund）
--           刷单数量（brush_order）
--           刷单金额（brush_amount）
--           佣金（commission）
--           转化率（conversion_rate）：买家数量/访客数量


DROP VIEW IF EXISTS v_sales_large_group;

CREATE VIEW v_sales_large_group
AS
SELECT 
    v_sales.sales_date sales_date,
    v_relation_team_largegroup.lg_no largegroup_no,
    v_relation_team_largegroup.lg_name largegroup_name,
    v_relation_team_largegroup.lg_header largegroup_header,
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales INNER JOIN v_relation_team_largegroup
    on v_sales.team_no = v_relation_team_largegroup.team_no
GROUP BY
    sales_date,
    largegroup_no,
    largegroup_name,
    largegroup_header
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    查询视图数据：

    /*
-- 数据查询语句
select * from v_sales_large_group
union
select '','','','总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
from v_sales;
*/

mysql> select * from v_sales_large_group
    -> union
    -> select '','','','总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率` 
    -> from v_sales;
+---------------------+---------------+--------------------------+-------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| sales_date          | largegroup_no | largegroup_name          | largegroup_header | visitors | buyers | pay        | refund    | brush_order | brush_amount | commission | conversion_rate |
+---------------------+---------------+--------------------------+-------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| 2022-01-01 00:00:00 | DZ01          | 电脑销售大组             | 刘备              |     5911 |   1907 |  214223.48 |  50787.37 |        1969 |     19356.17 |    6408.71 |          0.3226 |
| 2022-01-01 00:00:00 | DZ02          | 打印设备销售大组         | 曹操              |     7566 |   2142 |  215774.85 |  57763.32 |        2016 |     21596.72 |    6977.69 |          0.2831 |
| 2022-01-02 00:00:00 | DZ01          | 电脑销售大组             | 刘备              |     5916 |   2095 |  193022.12 |  52072.08 |        2017 |     19229.20 |    7048.70 |          0.3541 |
| 2022-01-02 00:00:00 | DZ02          | 打印设备销售大组         | 曹操              |     8759 |   2595 |  270044.20 |  52905.14 |        2006 |     21596.71 |    7450.07 |          0.2963 |
| 2022-01-03 00:00:00 | DZ01          | 电脑销售大组             | 刘备              |     5946 |   2014 |  178198.55 |  55796.98 |        1985 |     19166.24 |    6626.81 |          0.3387 |
| 2022-01-03 00:00:00 | DZ02          | 打印设备销售大组         | 曹操              |     7647 |   2269 |  245816.60 |  50852.25 |        1980 |     21091.77 |    7360.63 |          0.2967 |
|                     |               |                          | 总计              |    41745 |  13022 | 1317079.80 | 320177.14 |       11973 |    122036.81 |   41872.61 |          0.3119 |
+---------------------+---------------+--------------------------+-------------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
7 rows in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    8、按品牌分组统计

    创建视图的SQL语句如下：

    -- 6、按品牌分组统计
--    视图名：v_sales_brand
--    说明：统计每个品牌每天的销售信息
--    视图包含：销售日期（sales_date）
--              品牌编号（brand_no）
--              品牌名称（brand_name）
--              访客数量（visitors）
--              买家数量（buyers）
--              支付金额（pay）
--              退款金额（refund）
--              刷单数量（brush_order）
--              刷单金额（brush_amount）
--              佣金（commission）
--              转化率（conversion_rate）：买家数量/访客数量


DROP VIEW IF EXISTS v_sales_brand;

CREATE VIEW v_sales_brand
AS
SELECT 
    v_sales.sales_date sales_date,
    v_relation_product_brand.brand_no brand_no,
    v_relation_product_brand.brand_name brand_name,
    SUM(v_sales.visitors) visitors,
    SUM(v_sales.buyers) buyers,
    SUM(v_sales.pay) pay,
    SUM(v_sales.refund) refund,
    SUM(v_sales.brush_order) brush_order,
    SUM(v_sales.brush_amount) brush_amount,
    SUM(v_sales.commission) commission,
    ROUND(SUM(v_sales.buyers)/SUM(v_sales.visitors),4) conversion_rate
FROM 
    v_sales INNER JOIN v_relation_product_brand
    on v_sales.product_no = v_relation_product_brand.product_no
GROUP BY
    sales_date,
    brand_no,
    brand_name
;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    查询视图数据：

    /*
-- 数据查询语句
select * from v_sales_brand
union
select '','','总计',
       sum(visitors) `总访客数`,
       sum(buyers) `总买家数`,
       sum(pay) `支付总金额`,
       sum(refund) `退款总金额`,
       sum(brush_order) `总刷单数`,
       sum(brush_amount) `总刷单金额`,
       sum(commission) `总佣金`,
       ROUND(sum(buyers)/sum(visitors),4) `总转化率`
from v_sales;
*/

mysql> select * from v_sales_brand
    -> union
    -> select '','','总计',
    ->        sum(visitors) `总访客数`,
    ->        sum(buyers) `总买家数`,
    ->        sum(pay) `支付总金额`,
    ->        sum(refund) `退款总金额`,
    ->        sum(brush_order) `总刷单数`,
    ->        sum(brush_amount) `总刷单金额`,
    ->        sum(commission) `总佣金`,
    ->        ROUND(sum(buyers)/sum(visitors),4) `总转化率`
    -> from v_sales;
+---------------------+----------+------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| sales_date          | brand_no | brand_name | visitors | buyers | pay        | refund    | brush_order | brush_amount | commission | conversion_rate |
+---------------------+----------+------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
| 2022-01-01 00:00:00 | B101     | 方正       |     1606 |    477 |   49624.64 |  13889.35 |         477 |      5231.34 |    1867.87 |          0.2970 |
| 2022-01-01 00:00:00 | B102     | 神州       |     1492 |    534 |   54520.67 |  11967.26 |         518 |      4896.22 |    1394.38 |          0.3579 |
| 2022-01-01 00:00:00 | B201     | 戴尔       |     1268 |    393 |   57601.96 |  12545.46 |         445 |      4431.40 |    1358.16 |          0.3099 |
| 2022-01-01 00:00:00 | B202     | 惠普       |     1545 |    503 |   52476.21 |  12385.30 |         529 |      4797.21 |    1788.30 |          0.3256 |
| 2022-01-01 00:00:00 | B301     | 联想       |     1423 |    491 |   50279.14 |  14741.22 |         521 |      3747.23 |    2395.40 |          0.3450 |
| 2022-01-01 00:00:00 | B302     | 兄弟       |     1485 |    426 |   48934.98 |  12624.45 |         462 |      5536.37 |    1488.40 |          0.2869 |
| 2022-01-01 00:00:00 | B401     | 京瓷       |     1920 |    560 |   53918.51 |  13692.41 |         477 |      5709.84 |    1264.90 |          0.2917 |
| 2022-01-01 00:00:00 | B402     | 夏普       |     2738 |    665 |   62642.22 |  16705.24 |         556 |      6603.28 |    1828.99 |          0.2429 |
| 2022-01-02 00:00:00 | B101     | 方正       |     1612 |    481 |   44794.70 |  12720.32 |         549 |      5182.77 |    1830.74 |          0.2984 |
| 2022-01-02 00:00:00 | B102     | 神州       |     1261 |    495 |   43418.65 |  14278.92 |         452 |      5589.83 |    2313.39 |          0.3925 |
| 2022-01-02 00:00:00 | B201     | 戴尔       |     1698 |    570 |   55210.77 |  14579.02 |         540 |      3943.40 |    1342.04 |          0.3357 |
| 2022-01-02 00:00:00 | B202     | 惠普       |     1345 |    549 |   49598.00 |  10493.82 |         476 |      4513.20 |    1562.53 |          0.4082 |
| 2022-01-02 00:00:00 | B301     | 联想       |     1563 |    614 |   66235.32 |  13382.01 |         577 |      5072.52 |    1712.49 |          0.3928 |
| 2022-01-02 00:00:00 | B302     | 兄弟       |     1543 |    613 |   53393.99 |  14300.47 |         449 |      4414.66 |    1393.34 |          0.3973 |
| 2022-01-02 00:00:00 | B401     | 京瓷       |     2030 |    701 |   91250.84 |  12510.36 |         492 |      6875.64 |    1930.66 |          0.3453 |
| 2022-01-02 00:00:00 | B402     | 夏普       |     3623 |    667 |   59164.05 |  12712.30 |         488 |      5233.89 |    2413.58 |          0.1841 |
| 2022-01-03 00:00:00 | B101     | 方正       |     1724 |    531 |   52323.65 |  12657.95 |         436 |      4597.27 |    1815.06 |          0.3080 |
| 2022-01-03 00:00:00 | B102     | 神州       |     1401 |    503 |   37984.83 |  16455.03 |         522 |      3981.38 |    1048.20 |          0.3590 |
| 2022-01-03 00:00:00 | B201     | 戴尔       |     1567 |    568 |   44866.26 |  14215.55 |         492 |      5751.37 |    1825.09 |          0.3625 |
| 2022-01-03 00:00:00 | B202     | 惠普       |     1254 |    412 |   43023.81 |  12468.45 |         535 |      4836.22 |    1938.46 |          0.3285 |
| 2022-01-03 00:00:00 | B301     | 联想       |     1520 |    647 |   45118.43 |  12425.80 |         496 |      4735.59 |    1546.58 |          0.4257 |
| 2022-01-03 00:00:00 | B302     | 兄弟       |     1467 |    590 |   65052.16 |  13567.61 |         532 |      3770.65 |    2074.53 |          0.4022 |
| 2022-01-03 00:00:00 | B401     | 京瓷       |     2832 |    464 |   70322.48 |  11314.21 |         483 |      6009.42 |    1887.65 |          0.1638 |
| 2022-01-03 00:00:00 | B402     | 夏普       |     1828 |    568 |   65323.53 |  13544.63 |         469 |      6576.11 |    1851.87 |          0.3107 |
|                     |          | 总计       |    41745 |  13022 | 1317079.80 | 320177.14 |       11973 |    122036.81 |   41872.61 |          0.3119 |
+---------------------+----------+------------+----------+--------+------------+-----------+-------------+--------------+------------+-----------------+
25 rows in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
  • 相关阅读:
    vm16安装ubuntuServer22.04并安装docker
    [书籍翻译]12周撰写期刊文章 学术出版成功指南——第 5 周：回顾相关文献
    Jira使用浅谈篇一
    【勇敢饭饭，不怕刷题之链表】链表中有环的问题
    Spark第一课
    ICLR 21： SSGC SIMPLE SPECTRAL GRAPH CONVOLUTION
    Vue3学习系列之《列表渲染》
    Java Html转Word
    回顾.NET系列：Framework、Net Core、Net 过往
    网络工程师----第三十六天
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/128080125