|NO.Z.00037|——————————|BigDataEnd|——|Hadoop&实时数仓.V17|——|项目.v17|需求一：数据处理&全量查询.V1|——|需求分析|

一、需求1 :查询城市、省份、订单总额、订单总数----全量查询

### --- ODS层数层数据处理：yanqi_trade_orders

~~~     ods层数据处理：同第三部分
~~~     ods：mysql--（binlog） canal----kafka——>yanqi_trade_orders
~~~     ods：mysql：yanqi_trade_orders        下沉        hbash：yanqi_trade_orders
~~~     程序：kafkaToHBash    SinkHBase

### --- ODS层数层数据处理：yanqi_area

~~~     ods层数据处理：同第四部分
~~~     ods：mysql--（binlog） canal----kafka——>yanqi_area
~~~     ods：mysql：yanqi_area                    下沉        hbash：yanqi_area
~~~     程序：kafkaToHBash    SinkHBase

### --- DIM层数据处理：dim_yanqi_area：生成地域宽表

~~~     dwd：生成了区、市、省三级的明细宽表，保存在HBase中:dim_yanqi_area
~~~     dwd：hbash：yanqi_area                    生成        hbash：dim_yanqi_area    地域宽表
~~~     程序：AreaDetail    AreaDetailInfo    AimArea    HBASEReader    HBASEWriterSink

### --- dws层数据处理
### --- ADS层数据处理：将数据下沉到redis中：

~~~     dws : 统计城市、省份的订单总额，
~~~     订单总额和订单总数：orderNo、userId、status、totalMoney、areaId
~~~     dws：hbash：yanqi_trade_orders+dim_yanqi_area    生成    flink：table中临时表
~~~     程序：TotalCityOrder    ReadDimArea    ReadTradOrder

---

一、相关表数据sql说明

### --- 从yanqi_trade_orders表数据

mysql> SELECT * FROM yanqi_trade_orders;
+---------+-------------+--------+--------+--------------+------------+-----------+-------+--------+----------+-----------+----------+----------+---------------------+---------------------+---------------------+
| orderId | orderNo     | userId | status | productMoney | totalMoney | payMethod | isPay | areaId | tradeSrc | tradeType | isRefund | dataFlag | createTime          | payTime             | modifiedTime        |
+---------+-------------+--------+--------+--------------+------------+-----------+-------+--------+----------+-----------+----------+----------+---------------------+---------------------+---------------------+
|       1 | 23a0b124546 |     98 |      2 |         0.12 |   10468.00 |         2 |     0 | 370203 |        0 |         0 |        1 |        2 | 2020-06-28 18:14:01 | 2020-06-28 18:14:01 | 2020-10-21 22:54:31 |
|       2 | 23a0b124546 |    121 |      2 |         0.12 |    6331.00 |         2 |     0 | 370203 |        0 |         0 |        0 |        1 | 2020-06-28 16:55:02 | 2020-06-28 16:55:02 | 2020-10-21 22:54:32 |
|       3 | 23a0b124546 |     35 |      2 |         0.12 |    1987.50 |         4 |     0 | 370203 |        0 |         0 |        0 |        1 | 2020-06-28 12:07:01 | 2020-06-28 12:07:01 | 2020-10-21 22:54:34 |
|       4 | 23a0b124546 |    161 |      2 |         0.12 |   43659.00 |         4 |     0 | 370203 |        0 |         0 |        0 |        1 | 2020-06-28 13:19:48 | 2020-06-28 13:19:48 | 2020-10-21 22:54:35 |
|       5 | 23a0b124546 |     72 |      2 |         0.12 |   32757.00 |         0 |     0 | 370203 |        0 |         0 |        0 |        1 | 2020-06-28 22:14:21 | 2020-06-28 22:14:21 | 2020-10-21 22:54:37 |
+---------+-------------+--------+--------+--------------+------------+-----------+-------+--------+----------+-----------+----------+----------+---------------------+---------------------+---------------------+

### --- 从mysql：yanqi_area -- dim_yanqi_area维表

mysql> select * from dim_yanqi_area limit 5;
+--------+-----------+--------+-----------+--------+----------+
| areaid | aname     | cid    | city      | proid  | province |
+--------+-----------+--------+-----------+--------+----------+
| 110100 | 北京市    | 110000 | 北京      | 100000 | 中国     |
| 110101 | 东城区    | 110100 | 北京市    | 110000 | 北京     |
| 110102 | 西城区    | 110100 | 北京市    | 110000 | 北京     |
| 110105 | 朝阳区    | 110100 | 北京市    | 110000 | 北京     |
| 110106 | 丰台区    | 110100 | 北京市    | 110000 | 北京     |
+--------+-----------+--------+-----------+--------+----------+

### --- sql语句：

~~~     # sql语句
mysql> SELECT f.city,f.province,SUM(f.qusum) AS orderMoney, SUM(f.qucount) AS orderCount FROM
    -> (SELECT r.aname AS qu,r.city AS city,r.province AS province,SUM(k.totalMoney) AS qusum,COUNT(k.totalMoney) AS qucount FROM myorders AS k INNER JOIN dim_yanqi_area AS r ON k.areaId = r.areaid
    -> GROUP BY r.aname,r.city,r.province) AS f GROUP BY f.city,f.province;
~~~输出参数
+-----------+-----------+------------+------------+
| city      | province  | orderMoney | orderCount |
+-----------+-----------+------------+------------+
| 北京市    | 北京       |   20460.00 |         12 |
| 青岛市    | 山东省     |   16799.00 |          2 |
+-----------+-----------+------------+------------+

~~~     # sql说明

val sql: String = """
|select f.city,f.province,SUM(f.qusum) as orderMoney ,SUM(f.qucount) as orderCount from
|(select k.aname as qu,k.city as city,k.province as province,SUM(r.totalMoney) as qusum,COUNT(r.totalMoney) as qucount
|from dwd_yanqi_area as k
|inner join yanqi_orders as r --- 根据 地区id作join
|on k.areaId=r.areaId
|GROUP BY k.aname,k.city,k.province) as f --- 根据三级地区分组，做聚合sum和count
|GROUP BY f.city,f.province --- 根据两级地区分区，做聚合sum，求订单总额
|""".stripMargin

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---