package matchs.praction.commerce

import org.apache.spark.sql.{SaveMode, SparkSession}

object IndexCompute {
?def main(args: Array[String]): Unit = {

? ?val spark = SparkSession.builder()
? ? ?.master("local[*]").appName("Compute")
? ? ?.enableHiveSupport().getOrCreate()

? ?/*

? ?推薦使用網(wǎng)頁端觀看，移動端災(zāi)難現(xiàn)場

? ?注：與訂單金額計(jì)算相關(guān)使用order_money字段，同一個訂單無需多次重復(fù)計(jì)算，需要考慮退款或者取消的訂單
? ?第一題：
? 1、 根據(jù)dwd或者dws層表統(tǒng)計(jì)每人每天下單的數(shù)量和下單的總金額，
? ? ?存入dws層（需自建）的user_consumption_day_aggr表中（表結(jié)構(gòu)如下），
? ? ?然后使用hive cli按照客戶主鍵、訂單總金額均為降序排序，查詢出前5條；
? ? ? ?字段 類型 中文含義 備注
? ? ? ?customer_id ?int ?客戶主鍵 customer_id
? ? ? ?customer_name ?string 客戶名稱 customer_name
? ? ? ?total_amount double 訂單總金額 ?當(dāng)天訂單總金額
? ? ? ?total_count ?int ?訂單總數(shù) 當(dāng)天訂單總數(shù)
? ? ? ?year int ?年 ?訂單產(chǎn)生的年,為動態(tài)分區(qū)字段
? ? ? ?month ?int ?月 ?訂單產(chǎn)生的月,為動態(tài)分區(qū)字段
? ? ? ?day ?int ?日 ?訂單產(chǎn)生的日,為動態(tài)分區(qū)字段
? ? */

? ?spark.sql(
? ? ?"""
? ? ? ?|INSERT INTO TABLE n_dws.user_consumption_day_aggr
? ? ? ?|SELECT
? ? ? ?|fact.customer_id,
? ? ? ?|fact.customer_name,
? ? ? ?|SUM(fact.order_money) AS total_amount,
? ? ? ?|COUNT(1) AS total_count,
? ? ? ?|year,
? ? ? ?|month,
? ? ? ?|day
? ? ? ?|FROM(
? ? ? ?| ? ? SELECT
? ? ? ?| ? ? DISTINCT
? ? ? ?| ? ? CAST(fact.customer_id AS INT),
? ? ? ?| ? ? dim.customer_name,
? ? ? ?| ? ? fact.order_money,
? ? ? ?| ? ? CAST(SUBSTRING(fact.etl_date, 1, 4) AS INT) AS year,
? ? ? ?| ? ? CAST(SUBSTRING(fact.etl_date, 5, 2) AS INT) AS month,
? ? ? ?| ? ? CAST(SUBSTRING(fact.etl_date, 7, 2) AS INT) AS day
? ? ? ?| ? ? FROM n_dwd.fact_order_master fact JOIN n_dwd.dim_customer_inf dim
? ? ? ?| ? ? ON fact.order_status = '已下單'
? ? ? ?| ? ? AND fact.customer_id = dim.customer_id
? ? ? ?| ? ? AND fact.order_sn NOT IN(
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ?| ? ? )fact
? ? ? ?|GROUP BY fact.customer_id, fact.customer_name,
? ? ? ?|fact.year, fact.month, fact.day
? ? ? ?|""".stripMargin)

? ?spark.sql("SELECT * FROM n_dws.user_consumption_day_aggr ORDER BY customer_id DESC, total_amount DESC LIMIT 5").show()

? ?/*
? ?+-----------+-------------+------------+-----------+----+-----+---+
? ?|customer_id|customer_name|total_amount|total_count|year|month|day|
? ?+-----------+-------------+------------+-----------+----+-----+---+
? ?| ? ? ?19999| ? ? ? 吉秀芳| ? ? 2967.75| ? ? ? ? ?1|2022| ? ?4| 15|
? ?| ? ? ?19999| ? ? ? 吉秀芳| ? ? ?775.75| ? ? ? ? ?1|2022| ? ?5| ?7|
? ?| ? ? ?19998| ? ? ? 趙丹丹| ? ? 7215.19| ? ? ? ? ?1|2022| ? ?4| ?6|
? ?| ? ? ?19998| ? ? ? 趙丹丹| ? ? 3303.48| ? ? ? ? ?1|2022| ? ?4| ?2|
? ?| ? ? ?19997| ? ? ? ? 解娜| ? ?11474.22| ? ? ? ? ?1|2022| ? ?3| 30|
? ?+-----------+-------------+------------+-----------+----+-----+---+
? ? */


? ?/*
? ?第二題:
? ?2、 根據(jù)dwd或者dws層表統(tǒng)計(jì)每個城市每月下單的數(shù)量和下單的總金額（以order_master中的地址為判斷依據(jù)），
? ? ? 并按照province_name，year，month進(jìn)行分組,按照total_amount逆序排序，形成sequence值，
? ? ? 將計(jì)算結(jié)果存入Hive的dws數(shù)據(jù)庫city_consumption_day_aggr表中（表結(jié)構(gòu)如下），
? ? ? 然后使用hive cli根據(jù)訂單總數(shù)、訂單總金額均為降序排序，查詢出前5條，
? ? ? 在查詢時對于訂單總金額字段將其轉(zhuǎn)為bigint類型（避免用科學(xué)計(jì)數(shù)法展示）；
? ? ? ? ?字段 類型 中文含義 備注
? ? ? ? ?city_name ?string 城市名稱
? ? ? ? ?province_name ?string 省份名稱
? ? ? ? ?total_amount double 訂單總金額 ?當(dāng)月訂單總金額
? ? ? ? ?total_count ?int ?訂單總數(shù) 當(dāng)月訂單總數(shù)
? ? ? ? ?sequence int ?次序 即當(dāng)月中該城市消費(fèi)額在該省中的排名
? ? ? ? ?year int ?年 ?訂單產(chǎn)生的年,為動態(tài)分區(qū)字段
? ? ? ? ?month ?int ?月 ?訂單產(chǎn)生的月,為動態(tài)分區(qū)字段
? ? */

? ?spark.sql(
? ? ?"""
? ? ? ?|INSERT INTO TABLE n_dws.city_consumption_day_aggr
? ? ? ?|SELECT
? ? ? ?|city AS city_name,
? ? ? ?|province AS province_name,
? ? ? ?|CAST(SUM(order_money) AS BIGINT) AS total_amount,
? ? ? ?|CAST(COUNT(order_money) AS INT) AS total_count,
? ? ? ?|CAST(ROW_NUMBER() OVER(ORDER BY SUM(order_money) DESC) AS INT) AS sequence,
? ? ? ?|CAST(SUBSTRING(etl_date, 1, 4) AS INT) AS `year`,
? ? ? ?|CAST(SUBSTRING(etl_date, 5, 2) AS INT) AS `month`
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已下單'
? ? ? ?|AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? )
? ? ? ?|GROUP BY city, province,
? ? ? ?|SUBSTRING(etl_date, 1, 4),
? ? ? ?|SUBSTRING(etl_date, 5, 2)
? ? ? ?|""".stripMargin)

? ?// 優(yōu)化

/* ? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT city, province, SUM(order_money) sum_order, COUNT(1) cnt,
? ? ? ?|CAST(SUBSTRING(etl_date, 1, 4) AS INT) AS `year`,
? ? ? ?|CAST(SUBSTRING(etl_date, 5, 2) AS INT) AS `month`
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已退款'
? ? ? ?|GROUP BY city, province,
? ? ? ?|CAST(SUBSTRING(etl_date, 1, 4) AS INT),
? ? ? ?|CAST(SUBSTRING(etl_date, 5, 2) AS INT)
? ? ? ?|""".stripMargin).createTempView("de_order")

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT city, province, SUM(order_money) sum_order, COUNT(1) cnt,
? ? ? ?|CAST(SUBSTRING(etl_date, 1, 4) AS INT) AS `year`,
? ? ? ?|CAST(SUBSTRING(etl_date, 5, 2) AS INT) AS `month`
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已下單'
? ? ? ?|GROUP BY city, province,
? ? ? ?|CAST(SUBSTRING(etl_date, 1, 4) AS INT),
? ? ? ?|CAST(SUBSTRING(etl_date, 5, 2) AS INT)
? ? ? ?|""".stripMargin).createTempView("or_order")*/

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|or_order.city AS city_name,
? ? ? ?|or_order.province AS province_name,
? ? ? ?|CAST((or_order.sum_order - de_order.sum_order) AS BIGINT) AS total_amount,
? ? ? ?|CAST((or_order.cnt - de_order.cnt) AS INT) AS total_count,
? ? ? ?|ROW_NUMBER() OVER(ORDER BY (or_order.sum_order - de_order.sum_order) DESC) AS sequence,
? ? ? ?|or_order.`year`,
? ? ? ?|or_order.`month`
? ? ? ?|FROM (
? ? ? ?| ? ? ?SELECT city, province, SUM(order_money) sum_order, COUNT(1) cnt,
? ? ? ?| ? ? ?CAST(SUBSTRING(etl_date, 1, 4) AS INT) AS `year`,
? ? ? ?| ? ? ?CAST(SUBSTRING(etl_date, 5, 2) AS INT) AS `month`
? ? ? ?| ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ?WHERE order_status = '已下單'
? ? ? ?| ? ? ?GROUP BY city, province,
? ? ? ?| ? ? ?CAST(SUBSTRING(etl_date, 1, 4) AS INT),
? ? ? ?| ? ? ?CAST(SUBSTRING(etl_date, 5, 2) AS INT)
? ? ? ?| ? ? ?) AS or_order
? ? ? ?|JOIN (
? ? ? ?| ? ? ? SELECT city, province, SUM(order_money) sum_order, COUNT(1) cnt,
? ? ? ?| ? ? ? CAST(SUBSTRING(etl_date, 1, 4) AS INT) AS `year`,
? ? ? ?| ? ? ? CAST(SUBSTRING(etl_date, 5, 2) AS INT) AS `month`
? ? ? ?| ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? GROUP BY city, province,
? ? ? ?| ? ? ? CAST(SUBSTRING(etl_date, 1, 4) AS INT),
? ? ? ?| ? ? ? CAST(SUBSTRING(etl_date, 5, 2) AS INT)
? ? ? ?| ? ? ? ) AS de_order
? ? ? ?|ON de_order.city = or_order.city
? ? ? ?|AND de_order.province = or_order.province
? ? ? ?|AND de_order.`year` = or_order.`year`
? ? ? ?|AND de_order.`month` = or_order.`month`
? ? ? ?|""".stripMargin)


? ?spark.sql("SELECT * FROM n_dws.city_consumption_day_aggr ORDER BY total_count DESC, total_amount DESC LIMIT 5").show()

? ?/*
? ?+------------+-------------+------------+-----------+--------+----+-----+
? ?| ? city_name|province_name|total_amount|total_count|sequence|year|month|
? ?+------------+-------------+------------+-----------+--------+----+-----+
? ?| ? ? ?上海市| ? ? ? 上海市| ? 110372845| ? ? ?25762| ? ? ? 1|2022| ? ?4|
? ?| ? ? ?上海市| ? ? ? 上海市| ? ?33649186| ? ? ? 7861| ? ? ? 2|2022| ? ?3|
? ?| ? ? ?上海市| ? ? ? 上海市| ? ?28371160| ? ? ? 6739| ? ? ? 3|2022| ? ?5|
? ?| 浙江省杭州市| ? ? ? 浙江省| ? ?12745388| ? ? ? 3031| ? ? ? 4|2022| ? ?4|
? ?| 江蘇省南京市| ? ? ? 江蘇省| ? ?11586943| ? ? ? 2702| ? ? ? 5|2022| ? ?4|
? ?+------------+-------------+------------+-----------+--------+----+-----+
? ? */


? ?/*
? ?第三題:
? ?3、 請根據(jù)dwd或者dws層表計(jì)算出每個城市每個月平均訂單金額和該城市所在省份平均訂單金額相比較結(jié)果（“高/低/相同”）,
? ?存入ClickHouse數(shù)據(jù)庫shtd_result的cityavgcmpprovince表中（表結(jié)構(gòu)如下），
? ?然后在Linux的ClickHouse命令行中根據(jù)城市平均訂單金額、省份平均訂單金額均為降序排序，查詢出前5條；
? ? ?字段 類型 中文含義 備注
? ? ?cityname text 城市份名稱
? ? ?cityavgconsumption double 該城市平均訂單金額
? ? ?provincename text 省份名稱
? ? ?provinceavgconsumption double 該省平均訂單金額
? ? ?comparison text 比較結(jié)果 城市平均訂單金額和該省平均訂單金額比較結(jié)果，值為：高/低/相同
? ? */

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|city,
? ? ? ?|province,
? ? ? ?|AVG(order_money) AS cityavgconsumption
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已下單'
? ? ? ?|AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ?)
? ? ? ?|GROUP BY province, city
? ? ? ?|""".stripMargin).createTempView("city_avg")

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT province, AVG(order_money) AS provinceavgconsumption
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已下單'
? ? ? ?|AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? )
? ? ? ?|GROUP BY province
? ? ? ?|""".stripMargin).createTempView("province_avg")

? ?val no_3frame = spark.sql(
? ? ?"""
? ? ? ?|SELECT city_avg.city AS cityname,
? ? ? ?|ROUND(city_avg.cityavgconsumption, 2) AS cityavgconsumption,
? ? ? ?|province_avg.province AS provincename,
? ? ? ?|ROUND(province_avg.provinceavgconsumption, 2) AS provinceavgconsumption,
? ? ? ?|IF(
? ? ? ?| ? ROUND(city_avg.cityavgconsumption, 2)
? ? ? ?| ? ?>
? ? ? ?| ? ROUND(province_avg.provinceavgconsumption, 2),
? ? ? ?| ? '高', IF(
? ? ? ?| ? ? ? ? ? ROUND(city_avg.cityavgconsumption, 2)
? ? ? ?| ? ? ? ? ? <
? ? ? ?| ? ? ? ? ? ROUND(province_avg.provinceavgconsumption, 2),
? ? ? ?| ? ? ? ? ? ?'低', '相同'
? ? ? ?| ? ? ? ? ? ?)
? ? ? ?| ? ) AS comparison
? ? ? ?|FROM province_avg LEFT JOIN city_avg
? ? ? ?|ON province_avg.province = city_avg.province
? ? ? ?|""".stripMargin)

? ?// IF(boolean, a, IF(boolean, b, c)) 三元表達(dá)式

? ?no_3frame
? ? ?.write.mode(SaveMode.Append)
? ? ?.format("jdbc").option("url","jdbc:clickhouse://127.0.0.1:8123")
? ? ?.option("driver", "com.clickhouse.jdbc.ClickHouseDriver")
? ? ?.option("dbtable", "shtd_result.cityavgcmpprovince").save()

? ?// bigdata1 :) SELECT * FROM shtd_result.cityavgcmpprovince ORDER BY cityavgconsumption DESC, provinceavgconsumption DESC LIMIT 5;

? ?/*
? ?┌─cityname─┬─cityavgconsumption─┬─provincename─┬─provinceavgconsumption─┬─comparison─┐
? ?│ 江蘇省淮安市 │ ? ? ? ? ? ? 4580.81 │ ? ? ? ? ?江蘇省│ ? ? ? ? ? ? ? ?4228.88 │ ? ? ? ? ? ? 高│
? ?│ 浙江省嘉興市 │ ? ? ? ? ? ? 4580.79 │ ? ? ? ? ?浙江省│ ? ? ? ? ? ? ? ? 4229.4 │ ? ? ? ? ? ? 高│
? ?│ 浙江省慈溪市 │ ? ? ? ? ? ? 4567.32 │ ? ? ? ? ?浙江省│ ? ? ? ? ? ? ? ? 4229.4 │ ? ? ? ? ? ? 高│
? ?│ 江蘇省海門市 │ ? ? ? ? ? ? 4488.08 │ ? ? ? ? ?江蘇省│ ? ? ? ? ? ? ? ?4228.88 │ ? ? ? ? ? ? 高│
? ?│ 浙江省海寧市 │ ? ? ? ? ? ? 4463.39 │ ? ? ? ? ?浙江省│ ? ? ? ? ? ? ? ? 4229.4 │ ? ? ? ? ? ? 高│
? ?└───────┴─────────────┴─────────┴───────────────┴──────────┘
? ? */


?/*
? ?第四題：
? ? 4、請根據(jù)dwd或者dws層表計(jì)算出每個城市每個月平均訂單金額和該城市所在省份訂單金額中位數(shù)相比較結(jié)果（“高/低/相同”）,
? ? ? ? 存入ClickHouse數(shù)據(jù)庫shtd_result的citymidcmpprovince表中（表結(jié)構(gòu)如下），
? ? ? ? 然后在Linux的ClickHouse命令行中根據(jù)城市平均訂單金額、省份平均訂單金額均為降序排序，查詢出前5條；
? ? ? ? ? 字段 ?類型 中文含義 備注
? ? ? ? ? cityname ?text 城市份名稱
? ? ? ? ? citymidconsumption ?double 該城市訂單金額中位數(shù)
? ? ? ? ? provincename ?text 省份名稱
? ? ? ? ? provincemidconsumption ?double 該省訂單金額中位數(shù)
? ? ? ? ? comparison ?text 比較結(jié)果 城市訂單金額中位數(shù)和該省訂單金額中位數(shù)比較結(jié)果，值為：高/低/相同

? ? ? 注：此題題目與表結(jié)構(gòu)不一致，如按照題目求出每個城市每個月的平均訂單金額與城市所在省份訂單金額中位數(shù)比較結(jié)果，
? ? ? ? ?則缺少月份字段，結(jié)果毫無意義。這里給出兩種解法，第一種為：求出每個城市中位數(shù)金額與城市所在省份訂單金額中
? ? ? ? ?位數(shù)比較結(jié)果，第二種為：創(chuàng)建新的表格，包括進(jìn)月份及年份，求出每個城市每個月的平均訂單金額與城市所在省份
? ? ? ? ?訂單金額中位數(shù)比較結(jié)果
? ?*/

? ?// 第一種寫法

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT province, city, ROUND(AVG(order_money), 2) AS order_money
? ? ? ?|FROM (
? ? ? ?| ? ? ? SELECT province, city, order_money,
? ? ? ?| ? ? ? ROW_NUMBER() OVER(PARTITION BY province, city ORDER BY order_money) cnt,
? ? ? ?| ? ? ? COUNT(1) OVER(PARTITION BY province, city) cp_cnt
? ? ? ?| ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? WHERE order_status = '已下單'
? ? ? ?| ? ? ? AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? )
? ? ? ?| ? ? ? )tmp_b
? ? ? ?|WHERE cnt IN(FLOOR((cp_cnt+1)/2), FLOOR(cp_cnt+2)/2)
? ? ? ?|GROUP BY province, city
? ? ? ?|""".stripMargin).createTempView("city_median")

? ?/*
? ?ROUND(AVG(order_money), 2)
? ?AVG在此次用處為：
? ? ? ? ? ?中位數(shù)在取時，如果有偶數(shù)個訂單，會出現(xiàn)兩個中位數(shù)，此時
? ? ? ? ? ?需合并后相除取平均數(shù)（需省份和地區(qū)作分區(qū)字段）
? ?ROUND(a, b) 為四舍五入函數(shù)，a為需要四舍五入的數(shù)字，b為保留后幾位小數(shù)點(diǎn) 例：ROUND(11.987, 2) => 11.99
? ?FLOOR(a) 為向下取整函數(shù) 例：FLOOR(11.9) => 11
? ?*/

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT province, ROUND(AVG(order_money), 2) AS order_money
? ? ? ?|FROM (
? ? ? ?| ? ? ? SELECT province, order_money,
? ? ? ?| ? ? ? ROW_NUMBER() OVER(PARTITION BY province ORDER BY order_money) cnt,
? ? ? ?| ? ? ? COUNT(1) OVER(PARTITION BY province) cp_cnt
? ? ? ?| ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? WHERE order_status = '已下單'
? ? ? ?| ? ? ? AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ?| ? ? ? )tmp_a
? ? ? ?|WHERE cnt IN(FLOOR((cp_cnt+1)/2), FLOOR(cp_cnt+2)/2)
? ? ? ?|GROUP BY province
? ? ? ?|""".stripMargin).createTempView("province_median")

? ?val no_4frame = spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|city_median.city AS cityname,
? ? ? ?|city_median.order_money AS citymidconsumption,
? ? ? ?|province_median.province AS provincename,
? ? ? ?|province_median.order_money AS provincemidconsumption,
? ? ? ?|IF(
? ? ? ?| ? city_median.order_money > province_median.order_money,
? ? ? ?| ? '高', IF(city_median.order_money < province_median.order_money,'低', '相同')
? ? ? ?| ? ?) AS comparison
? ? ? ?|FROM city_median LEFT JOIN province_median
? ? ? ?|ON city_median.province = province_median.province
? ? ? ?|""".stripMargin)

? ?no_4frame
? ? ?.write.mode(SaveMode.Append)
? ? ?.format("jdbc").option("url", "jdbc:clickhouse://127.0.0.1:8123")
? ? ?.option("diver","com.clickhouse.jdbc.ClickHouseDriver")
? ? ?.option("dbtable", "shtd_result.citymidcmpprovince").save()

? ?// bigdata1 :) SELECT * FROM citymidcmpprovince ORDER BY citymidconsumption DESC, provincemidconsumption DESC LIMIT 5;

? ?/*
? ?┌─cityname─────┬─citymidconsumption─┬─provincename─┬─provincemidconsumption─┬─comparison─┐
? ?│ ? ? ? 浙江省上虞市 │ ? ? ? ? ? ?4384.39 │ ? ? ? ?浙江省 ? │ ? ? ? ? ? ? ? ? ?3925.6 │ ? ? ? ? ?高 ?│
? ?│ ? ? ? 江蘇省泰州市 │ ? ? ? ? ? ?4216.66 │ ? ? ? ?江蘇省 ? │ ? ? ? ? ? ? ? ? ?3931.4 │ ? ? ? ? ?高 ?│
? ?│ ? ? ? 江蘇省淮安市 │ ? ? ? ? ? ?4163.43 │ ? ? ? ?江蘇省 ? │ ? ? ? ? ? ? ? ? ?3931.4 │ ? ? ? ? ?高 ?│
? ?│ ? ? ? 江蘇省揚(yáng)州市 │ ? ? ? ? ? ?4152.15 │ ? ? ? ?江蘇省 ? │ ? ? ? ? ? ? ? ? ?3931.4 │ ? ? ? ? ?高 ?│
? ?│ ? ? ? 江蘇省吳江市 │ ? ? ? ? ? ?4145.63 │ ? ? ? ?江蘇省 ? │ ? ? ? ? ? ? ? ? ?3931.4 │ ? ? ? ? ?高 ?│
? ?└───────────┴────────────┴──────────┴────────────────┴────────┘
? ? */


? ?// 第二種寫法
? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT province, city,
? ? ? ?|ROUND(AVG(order_money), 2) AS order_money,
? ? ? ?|SUBSTRING(etl_date, 1, 4) AS `year`,
? ? ? ?|SUBSTRING(etl_date, 5, 2) AS `month`
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已下單'
? ? ? ?|AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? )
? ? ? ?|GROUP BY province, city,
? ? ? ?|SUBSTRING(etl_date, 1, 4),
? ? ? ?|SUBSTRING(etl_date, 5, 2)
? ? ? ?|""".stripMargin).createTempView("avg_month")

? ?// 此處可不創(chuàng)建臨時視圖，直接使用第二題的答案表(dws.city_consumption_day_aggr)，這里為了保證每一題的獨(dú)立性，不使用

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT province,
? ? ? ?|ROUND(AVG(order_money), 2) AS order_money
? ? ? ?|FROM (
? ? ? ?|SELECT province, order_money,
? ? ? ?|ROW_NUMBER() OVER(PARTITION BY province ORDER BY province) cnt,
? ? ? ?|COUNT(1) OVER(PARTITION BY province) cp_cnt
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已下單'
? ? ? ?|AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ?| ? ? ? ) tmp_a
? ? ? ?|WHERE cnt IN(FLOOR((cp_cnt+1)/2), FLOOR(cp_cnt+2)/2)
? ? ? ?|GROUP BY province
? ? ? ?|""".stripMargin).createTempView("province_medians")

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|avg_month.city AS cityname,
? ? ? ?|avg_month.order_money AS citymidconsumption,
? ? ? ?|province_medians.province AS provincename,
? ? ? ?|province_medians.order_money AS provincemidconsumption,
? ? ? ?|IF(
? ? ? ?| ? avg_month.order_money
? ? ? ?| ? >
? ? ? ?| ? province_medians.order_money,
? ? ? ?| ? '高', IF(
? ? ? ?| ? ? ? ? ? ? avg_month.order_money
? ? ? ?| ? ? ? ? ? ? <
? ? ? ?| ? ? ? ? ? ? province_medians.order_money,
? ? ? ?| ? ? ? ? ? ? '低', '相同'
? ? ? ?| ? ? ? ? ? )
? ? ? ?| ? ) AS comparison,
? ? ? ?|avg_month.`year`,
? ? ? ?|avg_month.`month`
? ? ? ?|FROM province_medians LEFT JOIN avg_month
? ? ? ?|ON province_medians.province = avg_month.province
? ? ? ?|ORDER BY citymidconsumption DESC, provincemidconsumption DESC
? ? ? ?|""".stripMargin).show(5)

? ?/*
? ? ? ?+------------+------------------+------------+----------------------+----------+----+-----+
? ? ? ?| ? ?cityname|citymidconsumption|provincename|provincemidconsumption|comparison|year|month|
? ? ? ?+------------+------------------+------------+----------------------+----------+----+-----+
? ? ? ?|浙江省上虞市| ? ? ? ? ? ?8928.6| ? ? ?浙江省| ? ? ? ? ? ? ? ? ? 5495.2| ? ? ? ? 高|2022| ? 06|
? ? ? ?|江蘇省姜堰市| ? ? ? ? ? 7965.96| ? ? ?江蘇省| ? ? ? ? ? ? ? ? ?8221.04| ? ? ? ? 低|2022| ? 07|
? ? ? ?|江蘇省吳江市| ? ? ? ? ? 6556.79| ? ? ?江蘇省| ? ? ? ? ? ? ? ? ?8221.04| ? ? ? ? 低|2022| ? 08|
? ? ? ?|浙江省上虞市| ? ? ? ? ? 6184.59| ? ? ?浙江省| ? ? ? ? ? ? ? ? ? 5495.2| ? ? ? ? 高|2022| ? 07|
? ? ? ?|浙江省紹興市| ? ? ? ? ? 6163.55| ? ? ?浙江省| ? ? ? ? ? ? ? ? ? 5495.2| ? ? ? ? 高|2022| ? 07|
? ? ? ?+------------+------------------+------------+----------------------+----------+----+-----+
? ? */


? ?/*
? ?第五題：
? ?5、 請根據(jù)dwd或者dws層表來計(jì)算每個省份2022年訂單金額前3省份，
? ?依次存入ClickHouse數(shù)據(jù)庫shtd_result的regiontopthree表中（表結(jié)構(gòu)如下），
? ?然后在Linux的ClickHouse命令行中根據(jù)省份升序排序，查詢出前5條；
? ? ?字段 類型 中文含義 備注
? ? ?provincename text 省份名稱
? ? ?citynames ?text 城市名稱 用,分割顯示前三城市的name
? ? ?cityamount text 省份名稱 用,分割顯示前三城市的訂單金額（需要去除小數(shù)部分，使用四舍五入）
? ? */

? ?val no_5frame = spark.sql(
? ? ?"""
? ? ? ?|SELECT province provincename,
? ? ? ?|CONCAT_WS(',', COLLECT_SET(city)) AS citynames,
? ? ? ?|CONCAT_WS(',', COLLECT_SET(order_money)) AS cityamount
? ? ? ?|FROM (
? ? ? ?| ? ? ? SELECT province, city, FLOOR(SUM(order_money)) order_money,
? ? ? ?| ? ? ? ROW_NUMBER() OVER(PARTITION BY province ORDER BY SUM(order_money)) cnt
? ? ? ?| ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? WHERE order_status = '已下單'
? ? ? ?| ? ? ? AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? )
? ? ? ?| ? ? ? GROUP BY province, city
? ? ? ?| ? ? ? )
? ? ? ?|WHERE cnt <= 3
? ? ? ?|GROUP BY province
? ? ? ?|ORDER BY provincename ASC
? ? ? ?|""".stripMargin)

? ?no_5frame
? ? ?.write.mode(SaveMode.Append)
? ? ?.format("jdbc").option("url", "jdbc:clickhouse://127.0.0.1:8123")
? ? ?.option("diver", "com.clickhouse.jdbc.ClickHouseDriver")
? ? ?.option("dbtable", "shtd_result.regiontopthree").save()

? ?// COLLECT_SET(a)將分組中的a列轉(zhuǎn)為一個數(shù)組返回
? ?// CONCAT_WS(',', str) 將多個字符串連接成一個字符串，可以一次性指定分隔符


? ?// bigdata1 :) SELECT * FROM regiontopthree ORDER BY provincename ASC LIMIT 5;

? ?/*
? ?┌─provincename─┬────────citynames───────────────┬─cityamount───────┐
? ?│ 上海市 ? ? ? ? │ 上海市 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?│ 183764909 ? ? ? ? ? ?│
? ?│ 廣東省 ? ? ? ? │ 廣東省河源市 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? │ 1142416 ? ? ? ? ? ? ?│
? ?│ 江蘇省 ? ? ? ? │ 江蘇省江陰市,江蘇省鎮(zhèn)江市,江蘇省溧陽市 ? ? ? ? ? ? │ 859475,822571,936577 │
? ?│ 浙江省 ? ? ? ? │ 浙江省奉化市,浙江省上虞市,浙江省金華市 ? ? ? ? ? ? │ 916449,824200,907457 │
? ?│ 貴州省 ? ? ? ? │ 貴州省貴陽市 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?│ 4226817 ? ? ? ? ? ? ?│
? ?└─────────┴────────────────────────────┴──────────────┘
? ? */


? ?/*
? ?第六題：
? ?6、請根據(jù)dwd或者dws層的相關(guān)表，計(jì)算銷售量前10的商品，
? ?銷售額前10的商品，存入ClickHouse數(shù)據(jù)庫shtd_result的topten表中（表結(jié)構(gòu)如下），
? ?然后在Linux的ClickHouse命令行中根據(jù)排名升序排序，查詢出前5條;
? ? ?字段 類型 中文含義 備注
? ? ?topquantityid ?int ?商品id 銷售量前10的商品
? ? ?topquantityname ?text 商品名稱 銷售量前10的商品
? ? ?topquantity ?int ?該商品銷售量 銷售量前10的商品
? ? ?toppriceid text 商品id 銷售額前10的商品
? ? ?toppricename text 商品名稱 銷售額前10的商品
? ? ?topprice decimal ?該商品銷售額 銷售額前10的商品
? ? ?sequence int ?排名 所屬排名
? ? */

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|product_id AS topquantityid,
? ? ? ?|product_name AS topquantityname,
? ? ? ?|SUM(product_cnt) AS topquantity,
? ? ? ?|ROW_NUMBER() OVER(ORDER BY SUM(product_cnt) DESC) AS sequence
? ? ? ?|FROM n_dwd.fact_order_detail
? ? ? ?|WHERE order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ?|GROUP BY product_id, product_name
? ? ? ?|""".stripMargin).createTempView("sell_count")

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|product_id AS toppriceid,
? ? ? ?|product_name AS toppricename,
? ? ? ?|ROUND(product_price * SUM(product_cnt), 2) AS topprice,
? ? ? ?|ROW_NUMBER() OVER(ORDER BY product_price * SUM(product_cnt) DESC) AS sequence
? ? ? ?|FROM n_dwd.fact_order_detail
? ? ? ?|WHERE order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? )
? ? ? ?|GROUP BY product_id, product_name, product_price
? ? ? ?|""".stripMargin).createTempView("sell_amount")


? ?val no_6frame = spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|topquantityid,
? ? ? ?|topquantityname,
? ? ? ?|topquantity,
? ? ? ?|toppriceid,
? ? ? ?|toppricename,
? ? ? ?|topprice,
? ? ? ?|sell_count.sequence
? ? ? ?|FROM sell_count JOIN sell_amount
? ? ? ?|ON sell_count.sequence <= 10
? ? ? ?|AND sell_amount.sequence <= 10
? ? ? ?|AND sell_count.sequence = sell_amount.sequence
? ? ? ?|""".stripMargin)

? ?no_6frame
? ? ?.write.mode(SaveMode.Append)
? ? ?.format("jdbc").option("url", "jdbc:clickhouse://127.0.0.1:8123")
? ? ?.option("diver", "com.clickhouse.jdbc.ClickHouseDiver")
? ? ?.option("dbtable", "shtd_result.topten").save()

? ?// bigdata1 :) SELECT * FROM topten ORDER BY sequence LIMIT 5;

? ?/*
? ?┌─topquantityid─┬─topquantityname───────────┬─topquantity┬─toppriceid┬─toppricename──────────────┬─topprice─┬─sequence─┐
? ?│ ? ? ? ? ? 599 │ 正點(diǎn)原子藍(lán)牙4.2串口透傳模塊 ATK- ? ? ? │ ? ? ? ? 89 │ 599 ? ? ? ?│ 正點(diǎn)原子藍(lán)牙4.2串口透傳模塊 ATK- ? ? ? ? │ 77917.72 │ ? ? ? ? 1 │
? ?│ ? ? ? ? ?7122 │ 夏新無線藍(lán)牙耳機(jī)5.0單雙耳一對迷你隱形 ? ?│ ? ? ? ? 76 │ 11976 ? ? ?│ 爆款i12馬卡龍多彩磨砂金屬電鍍藍(lán)牙耳機(jī)5.0 ?│ 66334.68 │ ? ? ? ? ?2 │
? ?│ ? ? ? ? ? 322 │ 華為Nova雙耳真無線藍(lán)牙耳機(jī)Nova5 ? ? ? │ ? ? ? ? 74 │ 7122 ? ? ? │ 夏新無線藍(lán)牙耳機(jī)5.0單雙耳一對迷你隱形 ? ? ?│ ?64383.4 │ ? ? ? ? 3 │
? ?│ ? ? ? ? ?7009 │ 不入耳式藍(lán)牙耳機(jī)雙耳骨傳導(dǎo)無線運(yùn)動跑步概 │ ? ? ? ? 74 │ 9193 ? ? ? │ 傲古聲 藍(lán)牙耳機(jī)雙耳入耳式5.0蘋果安卓 ? ? ? │ 63572.52 │ ? ? ? ? 4 │
? ?│ ? ? ? ? ?9344 │ 大電量無線藍(lán)牙耳機(jī)超長續(xù)航雙耳蘋果華為真 │ ? ? ? ? 71 │ 9344 ? ? ? │ 大電量無線藍(lán)牙耳機(jī)超長續(xù)航雙耳蘋果華為真 ? ? │ 63331.29 │ ? ? ? ? 5 │
? ?└─────────┴─────────────────────┴────────┴───────┴────────────────────────┴──────┴───────┘
? ? */

? ?/*
? ?第七題：
? ?7、請根據(jù)dwd或者dws層的數(shù)據(jù)，請計(jì)算連續(xù)兩天下單的用戶與已下單用戶的占比，
? ? ? ?將結(jié)果存入ClickHouse數(shù)據(jù)庫shtd_result的userrepurchasedrate表中(表結(jié)構(gòu)如下)，
? ? ? ?然后在Linux的ClickHouse命令行中查詢結(jié)果數(shù)據(jù)；
? ? ? ? ? 字段 ?類型 中文含義 備注
? ? ? ? ? purchaseduser int ?下單人數(shù) 已下單人數(shù)
? ? ? ? ? repurchaseduser int ?連續(xù)下單人數(shù) 連續(xù)兩天下單的人數(shù)
? ? ? ? ? repurchaserate ?text 百占比 ?連續(xù)兩天下單人數(shù)/已下單人數(shù)百分比（保留1位小數(shù)，四舍五入，不足的補(bǔ)0）例如21.1%，或者32.0%
? ? */

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT COUNT(1) order_num
? ? ? ?|FROM (
? ? ? ?| ? ? ?SELECT customer_id, COUNT(1) days_count
? ? ? ?| ? ? ?FROM (
? ? ? ?| ? ? ? ? ? ?SELECT DISTINCT customer_id, etl_date,
? ? ? ?| ? ? ? ? ? ?DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY etl_date) date
? ? ? ?| ? ? ? ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ?WHERE order_status = '已下單'
? ? ? ?| ? ? ? ? ? ?AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? )
? ? ? ?| ? ? ? ? ? ? ) tmp_a
? ? ? ?| ? ? ? ?GROUP BY customer_id, etl_date - date
? ? ? ?| ? ? ? ?HAVING days_count > 1
? ? ? ?| ? ? ? ?ORDER BY customer_id
? ? ? ?| ? ? ? ) tmp_b
? ? ? ?|""".stripMargin).createTempView("continuous_order")

? ?val no_7frame = spark.sql(
? ? ?"""
? ? ? ?|SELECT total_num AS purchaseduser,
? ? ? ?|order_num AS repurchaseduser,
? ? ? ?|CONCAT(CAST(ROUND((order_num/total_num)*100, 1) AS STRING), '%') AS repurchaserate
? ? ? ?|FROM (
? ? ? ?| ? ? ?SELECT COUNT(1) total_num
? ? ? ?| ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ?WHERE order_status = '已下單'
? ? ? ?| ? ? ?AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ?| ? ? ?) tmp_a JOIN continuous_order
? ? ? ?|""".stripMargin)

? ?no_7frame
? ? ?.write.mode(SaveMode.Append)
? ? ?.format("jdbc").option("url", "jdbc:clickhouse://127.0.0.1:8123")
? ? ?.option("diver", "com.clickhouse.jdbc.ClickHouseDiver")
? ? ?.option("dbtable", "shtd_result.userrepurchasedrate").save()

? ?// bigdata1 :) SELECT * FROM userrepurchasedrate;

? ?/*
? ?┌─purchaseduser─┬─repurchaseduser─┬─repurchaserate─┐
? ?│ ? ? ? ? 73296 │ ? ? ? ? ? ? 3936 │ 5.4% ? ? ? ? ? ? ?│
? ?└─────────┴───────────┴────────────┘
? ? */


? ?/*
? ?第八題：
? ?8、根據(jù)dwd或者dws層的數(shù)據(jù)，請計(jì)算每個省份累計(jì)訂單量，然后根據(jù)每個省份訂單量從高到低排列，
? ?將結(jié)果打印到控制臺（使用spark中的show算子，同時需要顯示列名）；
? ? ? ?例如：可以考慮首先生成類似的臨時表A：
? ? ? ?province_name ?Amount（訂單量）
? ? ? ?A省 10122
? ? ? ?B省 301
? ? ? ?C省 2333333

? ? ? ?然后生成結(jié)果類似如下：其中C省銷量最高，排在第一列，A省次之，以此類推。
? ? ? ?C省 A省 B省
? ? ? ?23333331 10122 ?301
? ? */

? ?// 第一種方法

? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|SUM(IF(province = '上海市', amount, 0)) AS Shanghai,
? ? ? ?|SUM(IF(province = '江蘇省', amount, 0)) AS Jiangsu,
? ? ? ?|SUM(IF(province = '浙江省', amount, 0)) AS Zhejiang,
? ? ? ?|SUM(IF(province = '貴州省', amount, 0)) AS Guizhou,
? ? ? ?|SUM(IF(province = '廣東省', amount, 0)) AS Guangdong
? ? ? ?|FROM (
? ? ? ?| ? ? ? SELECT province, COUNT(order_money) amount
? ? ? ?| ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? WHERE order_status = '已下單'
? ? ? ?| ? ? ? AND order_sn NOT IN(
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? )
? ? ? ?| ? ? ? GROUP BY province
? ? ? ?| ? ? ? )
? ? ? ?|""".stripMargin).show()

? ?/*
? ?+--------+-------+--------+-------+---------+
? ?|Shanghai|Jiangsu|Zhejiang|Guizhou|Guangdong|
? ?+--------+-------+--------+-------+---------+
? ?| ? 42983| ?17353| ? 11736| ? ?965| ? ? ?259|
? ?+--------+-------+--------+-------+---------+
? ? */

? ?// 第二種方法
? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT
? ? ? ?|SUM(IF(province = '上海市', 1, 0)) AS Shanghai,
? ? ? ?|SUM(IF(province = '江蘇省', 1, 0)) AS Jiangsu,
? ? ? ?|SUM(IF(province = '浙江省', 1, 0)) AS Zhejiang,
? ? ? ?|SUM(IF(province = '貴州省', 1, 0)) AS Guizhou,
? ? ? ?|SUM(IF(province = '廣東省', 1, 0)) AS Guangdong
? ? ? ?|FROM n_dwd.fact_order_master
? ? ? ?|WHERE order_status = '已下單'
? ? ? ?|AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ?SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ?WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ?|""".stripMargin).show()

? ?/*
? ?+--------+-------+--------+-------+---------+
? ?|Shanghai|Jiangsu|Zhejiang|Guizhou|Guangdong|
? ?+--------+-------+--------+-------+---------+
? ?| ? 42983| ?17353| ? 11736| ? ?965| ? ? ?259|
? ?+--------+-------+--------+-------+---------+
? ?*/


? ?/*
? ?第九題：
? ?9、 根據(jù)dwd或者dws層的相關(guān)表，請計(jì)算2022年4月26日凌晨0點(diǎn)0分0秒到早上9點(diǎn)59分59秒為止，
? ?該時間段每小時的新增訂單金額與當(dāng)天訂單總金額累加值，存入ClickHouse數(shù)據(jù)庫shtd_result的
? ?accumulateconsumption表中，然后在Linux的ClickHouse命令行中根據(jù)訂單時間段升序排序，查詢出前5條;
? ? ? ?假如數(shù)據(jù)為：
? ? ? ?用戶 訂單時間 訂單金額
? ? ? ?張三1號 2020-04-26 00:00:10 ?10
? ? ? ?李四1號 2020-04-26 00:20:10 ?5
? ? ? ?李四2號 2020-04-26 01:21:10 ?10
? ? ? ?王五1號 2020-04-26 03:20:10 ?50

? ? ? ?計(jì)算結(jié)果則為：
? ? ? ?訂單時間段 ?訂單新增金額 累加總金額
? ? ? ?2020-04-26 00 ?15 15
? ? ? ?2020-04-26 01 ?10 25
? ? ? ?2020-04-26 02 ?0 ?25
? ? ? ?2020-04-26 03 ?50 75

? ? ? ?accumulateconsumption表結(jié)構(gòu)如下：
? ? ? ?字段 類型 中文含義 備注
? ? ? ?consumptiontime ?varchar ?消費(fèi)時間段
? ? ? ?consumptionadd double 訂單新增金額
? ? ? ?consumptionacc double 累加總金額
? ? */

? ?val no_9frame = spark.sql(
? ? ?"""
? ? ? ?|SELECT CONCAT('2022-04-26 0', hour_time) AS consumptiontime,
? ? ? ?|ROUND(hour_money, 2) AS consumptionadd,
? ? ? ?|CAST(SUM(hour_money) OVER(ORDER BY hour_time) AS BIGINT) AS consumptionacc
? ? ? ?|FROM(
? ? ? ?| ? ? ?SELECT HOUR(modified_time) hour_time, SUM(order_money) hour_money
? ? ? ?| ? ? ?FROM (
? ? ? ?| ? ? ? ? ? ?SELECT DISTINCT shipping_user, modified_time, order_money
? ? ? ?| ? ? ? ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ?WHERE order_status = '已下單'
? ? ? ?| ? ? ? ? ? ?AND modified_time BETWEEN '2022-04-26 00:00:00'
? ? ? ?| ? ? ? ? ? ?AND '2022-04-26 10:00:00'
? ? ? ?| ? ? ? ? ? ?AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? )
? ? ? ?| ? ? ? ? ? ?) tmp_a
? ? ? ?| ? ? ?GROUP BY HOUR(modified_time)
? ? ? ?| ? ? )tmp_b
? ? ? ?|UNION ALL
? ? ? ?|SELECT '2022-04-26 00', 0, 0
? ? ? ?|UNION ALL
? ? ? ?|SELECT '2022-04-26 01', 0, 0
? ? ? ?|UNION ALL
? ? ? ?|SELECT '2022-04-26 02', 0, 0
? ? ? ?|ORDER BY consumptiontime ASC
? ? ? ?|""".stripMargin)

? ?no_9frame
? ? ?.write.mode(SaveMode.Append)
? ? ?.format("jdbc").option("url", "jdbc:clickhouse://127.0.0.1:8123")
? ? ?.option("diver", "com.clickhouse.jdbc.ClickHouseDiver")
? ? ?.option("dbtable", "shtd_result.accumulateconsumption").save()

? ?// CAST(SUM(hour_money) OVER(ORDER BY hour_time) AS BIGINT)在此處的用處
? ?// CAST(a AS b) 將a字段轉(zhuǎn)換為b類型
? ?// 在SUM函數(shù)后加開窗排序，可得到累加的效果

? ?// OS:不開窗能想到的方法只有這個了，面向答案編程(bus)

? ?/*
? ?┌─consumptiontime─┬─consumptionadd─┬─consumptionacc─┐
? ?│ 2022-04-26 00 ? │ ? ? ? ? ? ? ?0 │ ? ? ? ? ? ? ? ? 0 │
? ?│ 2022-04-26 01 ? │ ? ? ? ? ? ? ?0 │ ? ? ? ? ? ? ? ? 0 │
? ?│ 2022-04-26 02 ? │ ? ? ? ? ? ? ?0 │ ? ? ? ? ? ? ? ? 0 │
? ?│ 2022-04-26 03 ? │ ? ? ? ? ?22723 │ ? ? ? ? ? ? 22723 │
? ?│ 2022-04-26 04 ? │ ? ? ? ? ? 4838 │ ? ? ? ? ? ? 27561 │
? ?└──────────┴──────────┴─────────────┘
? ? */


? ?/*
? ?第十題：
? ?10、 ?根據(jù)dwd層或dws層的相關(guān)表，請計(jì)算2022年4月26日凌晨0點(diǎn)0分0秒到早上9點(diǎn)59分59秒為止的數(shù)據(jù)，
? ?以5個小時為時間窗口，滑動的步長為1小時，做滑動窗口計(jì)算該窗口內(nèi)訂單總金額和訂單總量，時間不滿5小時
? ?不觸發(fā)計(jì)算（即從凌晨5點(diǎn)0分0秒開始觸發(fā)計(jì)算），存入ClickHouse數(shù)據(jù)庫shtd_result的
? ?slidewindowconsumption表中，然后在Linux的ClickHouse命令行中根據(jù)訂單時間段升序排序，
? ?查詢出前5條，將核心業(yè)務(wù)代碼中的開窗相關(guān)代碼與MySQL查詢結(jié)果展示出來。

? ? ?假如數(shù)據(jù)為：
? ? ?用戶 訂單時間 訂單金額
? ? ?張三1號 2020-04-26 00:00:10 ?10
? ? ?李四1號 2020-04-26 00:20:10 ?25
? ? ?李四2號 2020-04-26 01:21:10 ?10
? ? ?李四2號 2020-04-26 02:21:10 ?5
? ? ?王五1號 2020-04-26 03:20:10 ?20
? ? ?李四2號 2020-04-26 04:20:10 ?10
? ? ?王五2號 2020-04-26 05:10:10 ?10
? ? ?李四2號 2020-04-26 06:20:10 ?10
? ? ?趙六2號 2020-04-26 07:10:10 ?10
? ? ?趙六2號 2020-04-26 08:10:10 ?10
? ? ?王五2號 2020-04-26 09:11:10 ?10
? ? ?王五4號 2020-04-26 09:32:10 ?30

? ? ?計(jì)算結(jié)果則為：
? ? ?訂單時間段 ?該窗口內(nèi)訂單金額 訂單總量 平均每單價格
? ? ?2020-04-26 04 ?80 6 ?13.33
? ? ?2020-04-26 05 ?55 5 ?11
? ? ?2020-04-26 06 ?55 5 ?11
? ? ?2020-04-26 07 ?60 5 ?12
? ? ?2020-04-26 08 ?50 5 ?10
? ? ?2020-04-26 09 ?80 6 ?13.33

? ? ?slidewindowconsumption表結(jié)構(gòu)如下：
? ? ?字段 類型 中文含義 備注
? ? ?consumptiontime ?varchar ?訂單時間段
? ? ?consumptionsum double 該窗口內(nèi)的訂單總金額
? ? ?consumptioncount double 訂單總數(shù)量
? ? ?consumptionavg double 平均每單價格 上面兩個字段相除，四舍五入保留兩位小數(shù)
? ? */

? ?// UNIX_TIMESTAMP(A, B) 時間字段轉(zhuǎn)時間戳
? ?// A 代表類時間字段， B 代表時間字段的格式

? ?// FROM_UNIXTIME(A, B) 時間戳轉(zhuǎn)時間格式
? ?// A 代表類時間戳， B 代表要轉(zhuǎn)成的時間格式


? ?spark.sql(
? ? ?"""
? ? ? ?|SELECT order_money,
? ? ? ?|FROM_UNIXTIME(UNIX_TIMESTAMP(modified_time, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd HH') AS modified_time
? ? ? ?|FROM (
? ? ? ?| ? ? ? SELECT DISTINCT modified_time, order_money
? ? ? ?| ? ? ? FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? WHERE order_status = '已下單'
? ? ? ?| ? ? ? AND modified_time BETWEEN '2022-04-26 00:00:00'
? ? ? ?| ? ? ? AND '2022-04-26 10:00:00'
? ? ? ?| ? ? ? AND order_sn NOT IN (
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?SELECT order_sn
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM n_dwd.fact_order_master
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE order_status = '已退款'
? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ?| ? ? ? ) tmp_a
? ? ? ?|""".stripMargin).createTempView("fiver_order")

? ?val no_10frame = spark.sql(
? ? ?"""
? ? ? ?|SELECT consumptiontime,
? ? ? ?|SUM(consumptionsum) OVER(ORDER BY consumptiontime ROWS 4 PRECEDING) AS consumptionsum,
? ? ? ?|SUM(consumptioncount) OVER(ORDER BY consumptiontime ROWS 4 PRECEDING) AS consumptioncount,
? ? ? ?|AVG(consumptionavg) OVER(ORDER BY consumptiontime ROWS 4 PRECEDING) AS consumptionavg
? ? ? ?|FROM (
? ? ? ?| ? ? ? SELECT modified_time AS consumptiontime,
? ? ? ?| ? ? ? SUM(order_money) AS consumptionsum,
? ? ? ?| ? ? ? COUNT(order_money) AS consumptioncount,
? ? ? ?| ? ? ? AVG(order_money) AS consumptionavg
? ? ? ?| ? ? ? FROM fiver_order
? ? ? ?| ? ? ? GROUP BY modified_time
? ? ? ?| ? ? ? ) tmp_a
? ? ? ?|WHERE HOUR(consumptiontime) > 4
? ? ? ?|""".stripMargin)

? ?no_10frame.write.mode(SaveMode.Append)
? ? ?.format("jdbc").option("url", "jdbc:clickhouse://127.0.0.1:8123")
? ? ?.option("diver", "com.jdbc.clickhouse.ClickHouseDiver")
? ? ?.option("dbtable", "shtd_result.slidewindowconsumption").save()

? ?// bigdata1 :) SELECT * FROM slidewindowconsumption ORDER BY consumptiontime ASC LIMIT 5;

? ?/*
? ?┌─consumptiontime─┬──consumptionsum─┬─consumptioncount─┬───consumptionavg─┐
? ?│ 2022-04-26 05 ? │ ? ? ? ? ?184952.67 │ ? ? ? ? ? ? ? 44 │ ? ? ? ? ? ? 4203.46 │
? ?│ 2022-04-26 06 ? │ ? ? ? ? ?557175.09 │ ? ? ? ? ? ? ?128 │ ? ? ? ? ? ?4317.34 │
? ?│ 2022-04-26 07 ? │ ? ? ? ? ?890390.89 │ ? ? ? ? ? ? ?211 │ ? ? ? ? ? 4216.445 │
? ?│ 2022-04-26 08 ? │ ? ? ? ? 1282905.98 │ ? ? ? ? ? ? ?311 │ ? ? ? ? ? ?4143.62 │
? ?│ 2022-04-26 09 ? │ ? ? ? ? 1714063.44 │ ? ? ? ? ? ? ?403 │ ? ? ? ? ?4252.1965 │
? ?└──────────┴─────────────┴───────────┴─────────────┘
? ? */


? ?spark.stop()
? ?spark.close()

?}

}