def generate_brand_data():
try:
hbase = {"table": brand_table, "families": ["0"], "row": "brand_id"}
print(f"{dt.now()} 生成品牌名称 {brand_table}")
#写入品牌表
plm_item = get_plm_item(spark).select("item_id", "item_name",
"item_kind", "is_mrb")
# item_kind !=4 4为罚没 is_mrb=1 1为在使用 去掉空格 去掉* 中文括号用英文括号替换
#处理item_name
#??? 可以用所有数据 然后drop掉没有括号的
plm_item_brand = plm_item\
.where(col("item_kind") != "4") \
.where(col("is_mrb") == "1") \
.withColumn("brand_name",item_name_udf(col("item_name")))\
.dropDuplicates(["brand_name"]) \
.where(col("brand_name") != "")
from pyspark.sql import Window
win = Window.orderBy("item_id")
brand=plm_item_brand.select("item_id", "item_name", "brand_name") \
.withColumn("brand_id", f.row_number().over(win))
brand.foreachPartition(
lambda x: write_hbase1(x, ["brand_name"], hbase))
except:
tb.print_exc()
def get_brand_stats_info_monthly():
# 市/区每款卷烟上一月销量/环比/同比
# 市/区每款卷烟上一月订单数/环比/同比
# 市/区每款卷烟上一月销量占比/环比/同比
try:
# 烟品牌id，烟品牌名称
brand = get_phoenix_table(spark, brand_table)
# 烟id，烟名称
plm_item = get_plm_item(spark).select("item_id", "item_name")
area = get_area(spark)
# com_id与city的映射关系
city = area.dropDuplicates(["com_id"]).select("com_id", "city")
# sale_center_id与区(list)的映射关系
county = area.groupBy("sale_center_id") \
.agg(f.collect_list("county").alias("county")) \
.select("sale_center_id", "county")
# 标识列的值
markers = ["0", "2"]
# 按照 市或区统计
groups = ["com_id", "sale_center_id"]
joins = [city, county]
# 除需要计算的值，其他的数据
cols_comm = [["city", "brand_id", "brand_name", "ciga_data_marker"],
[
"county", "sale_center_id", "brand_id", "brand_name",
"ciga_data_marker"
]]
#需要计算的值的列名
cols = [[
"brand_city_month_sales", "brand_city_month_orders",
"brand_city_month_sales_ratio", "brand_city_month2_sales",
"brand_city_month2_orders", "brand_city_month2_sales_ratio",
"brand_city_month_sales_last_year",
"brand_city_month_orders_last_year",
"brand_city_month_retio_last_year"
],
[
"brand_county_month_sales", "brand_county_month_orders",
"brand_county_month_sales_ratio",
"brand_county_month2_sales", "brand_county_month2_orders",
"brand_county_month2_sales_ratio",
"brand_county_month_sales_last_year",
"brand_county_month_orders_last_year",
"brand_county_month_retio_last_year"
]]
co_co_line=get_co_co_line(spark, scope=[1, 13], filter="month") \
.where(col("month_diff").isin([1,2,13]))\
.join(plm_item, "item_id") \
.withColumn("brand_name", item_name_udf(col("item_name"))) \
.select("brand_name", "com_id", "sale_center_id", "qty_ord","month_diff")
co_co_line.cache()
for i in range(len(groups)):
group = groups[i]
join = joins[i]
c = cols[i]
marker = markers[i]
try:
# 获取上一个月订单行表数据
last_month = co_co_line.where(col("month_diff") == 1)
# 1、2.市/区每款烟的订单量，订单数
print(f"{str(dt.now())} 上一个月{group}每个品牌的订单量，订单数")
qty_amt = last_month.groupBy(group, "brand_name").agg(
f.sum(col("qty_ord")).alias(c[0]),
f.count(col(group)).alias(c[1]))
# 3.市/区每款卷烟销量占比
print(f"{str(dt.now())} 上一个月{group}每个品牌的销量占比")
qty_ratio = last_month.groupBy(group) \
.agg(f.sum(col("qty_ord")).alias("qty_ord_total")) \
.join(qty_amt, group) \
.withColumn(c[2], col(c[0]) / col("qty_ord_total"))\
.select(group,"brand_name",c[2])
# --------------------环比
# 获取上第二个月订单行表数据
last_two_month = co_co_line.where(col("month_diff") == 2)
# 市/区每款卷烟订单量，订单数 上次同期
print(f"{str(dt.now())} 上第二个月{group}每个品牌的订单量，订单数")
qty_amt_last = last_two_month.groupBy(group, "brand_name").agg(
f.sum(col("qty_ord")).alias(c[3]),
f.count(col(group)).alias(c[4]))
# 市/区每款卷烟销量占比 上次同期
print(f"{str(dt.now())} 上第二个月{group}每个品牌的销量占比")
qty_ratio_last = last_two_month.groupBy(group) \
.agg(f.sum(col("qty_ord")).alias("qty_ord_total_last")) \
.join(qty_amt_last, group) \
.withColumn(c[5], col(c[3]) / col("qty_ord_total_last"))\
.select(group,"brand_name",c[5])
# # 4、5.市/区每款卷烟订单量，订单数环比
# print(f"{str(dt.now())} 上一个月{group}每个品牌的订单量，订单数的环比")
# qty_amt_last.join(qty_amt, [group, "brand"]) \
# .withColumn("qty_ring_rate", period_udf(col("qty_ord"), col("qty_ord_last"))) \
# .withColumn("amt_ring_rate", period_udf(col("amount"), col("amount_last")))
#
# # 6.市/区每款卷烟订单量占比环比
# print(f"{str(dt.now())} 上一个月{group}每个品牌的销量占比的环比")
# qty_ratio_last.join(qty_ratio, [group, "brand"]) \
# .withColumn("qty_ratio_ring", period_udf(col("qty_ratio"), col("qty_ratio_last")))
# ---------------------同比
# 获取去年同期 上一月订单行表数据
last_year = co_co_line.where(col("month_diff") == 13)
# 市/区每款烟的订单量,订单数 去年同期
print(f"{str(dt.now())} 去年同期{group}每个品牌的订单量，订单数")
qty_amt_ly = last_year.groupBy(group, "brand_name") \
.agg(f.sum(col("qty_ord")).alias(c[6]), f.count(col(group)).alias(c[7]))
# 市/区每款烟销量的占比 去年同期
print(f"{str(dt.now())} 去年同期{group}每个品牌的销量占比")
qty_ratio_ly = last_year.groupBy(group) \
.agg(f.sum(col("qty_ord")).alias("qty_ord_total_ly")) \
.join(qty_amt_ly, group) \
.withColumn(c[8], col(c[6]) / col("qty_ord_total_ly"))\
.select(group,"brand_name",c[8])
# # 7、8. 市/区每款卷烟订单量,订单数 同比
# print(f"{str(dt.now())} 上一个月{group}每个品牌的订单量，订单数的同比")
# qty_amt_ly.join(qty_amt, [group, "brand"]) \
# .withColumn("qty_ord_yoy", period_udf(col("qty_ord"), col("qty_ord_ly"))) \
# .withColumn("amt_yoy", period_udf(col("amount"), col("amount_ly")))
# # 9.市/区每款烟销量占比的同比
# print(f"{str(dt.now())} 上一个月{group}每个品牌的销量占比的同比")
# qty_ratio_ly.join(qty_ratio, [group, "brand"]) \
# .withColumn("qty_ratio_yoy", period_udf(col("qty_ratio"), col("qty_ratio_ly")))
all_df = qty_ratio.join(qty_amt, [group, "brand_name"], "outer") \
.join(qty_amt_last, [group, "brand_name"], "outer") \
.join(qty_ratio_last, [group, "brand_name"], "outer") \
.join(qty_amt_ly, [group, "brand_name"], "outer") \
.join(qty_ratio_ly, [group, "brand_name"], "outer")\
.na.fill(0,c)
columns = cols_comm[i] + c
all_df.join(brand, "brand_name") \
.withColumn("row", f.concat_ws("_", col(group), col("brand_id"))) \
.join(join, group) \
.withColumn("ciga_data_marker", f.lit(marker)) \
.foreachPartition(lambda x: write_hbase1(x, columns, hbase))
except Exception:
tb.print_exc()
co_co_line.unpersist()
except Exception:
tb.print_exc()
def get_item_ratio():
# 市/区 同品牌各规格销量占比
try:
# 烟品牌id，烟品牌名称
brand = get_phoenix_table(spark, brand_table)
# 烟id，烟名称
plm_item = get_plm_item(spark).select("item_id", "item_name")
area = get_area(spark)
# com_id与city的映射关系
city = area.dropDuplicates(["com_id"]).select("com_id", "city")
# sale_center_id与区(list)的映射关系
county = area.groupBy("sale_center_id") \
.agg(f.collect_list("county").alias("county")) \
.select("sale_center_id", "county")
#标识列的值
markers = ["1", "3"]
# 按照 市或区统计
groups = ["com_id", "sale_center_id"]
joins = [city, county]
# 除需要计算的值，其他的数据
cols_comm = [[
"city", "brand_id", "brand_name", "gauge_id", "gauge_name",
"ciga_data_marker"
],
[
"county", "sale_center_id", "brand_id", "brand_name",
"gauge_id", "gauge_name", "ciga_data_marker"
]]
# 需要计算的值的列名
cols = [
"brand_city_gauge_sales_ratio", "brand_county_gauge_sales_ratio"
]
# 1.上个月同品牌各规格销量占比
co_co_line = get_co_co_line(spark, scope=[1, 1], filter="month") \
.join(plm_item, "item_id") \
.withColumn("brand_name", item_name_udf(col("item_name"))) \
.select("item_id", "brand_name", "qty_ord", "com_id","sale_center_id")
co_co_line.cache()
for i in range(len(groups)):
group = groups[i]
join = joins[i]
c = cols[i]
marker = markers[i]
columns = cols_comm[i]
try:
#2.各市/区 各品牌销量
brand_ord = co_co_line.groupBy(group, "brand_name").agg(
f.sum(col("qty_ord")).alias("brand_ord"))
#3.各市/区 每个品牌各品规销量
item_ord = co_co_line.groupBy(
group, "brand_name",
"item_id").agg(f.sum(col("qty_ord")).alias("item_ord"))
#4.销量占比
print(f"{str(dt.now())} 上一个月{group}同品牌各品规的销量占比")
brand_item=brand_ord.join(item_ord, [group,"brand_name"]) \
.withColumn(c, col("item_ord") / col("brand_ord"))
columns.append(c)
brand_item.withColumn("row",f.concat_ws("_",col(group),col("item_id"))) \
.join(plm_item,"item_id")\
.join(brand, "brand_name")\
.join(join,group)\
.withColumn("ciga_data_marker",f.lit(marker))\
.withColumnRenamed("item_id","gauge_id")\
.withColumnRenamed("item_name","gauge_name")\
.foreachPartition(lambda x:write_hbase1(x,columns,hbase))
except Exception:
tb.print_exc()
co_co_line.unpersist()
except Exception:
tb.print_exc()
def get_brand_stats_info_daily():
#本月当前本市/区各品牌订货量
#本月当前本市/区各品牌订单额
try:
# 烟品牌id，烟品牌名称
brand = get_phoenix_table(spark, brand_table)
# 烟id，烟名称
plm_item = get_plm_item(spark).select("item_id", "item_name")
area = get_area(spark)
# com_id与city的映射关系
city = area.dropDuplicates(["com_id"]).select("com_id", "city")
# sale_center_id与区(list)的映射关系
county = area.groupBy("sale_center_id") \
.agg(f.collect_list("county").alias("county")) \
.select("sale_center_id", "county")
# 标识列的值
markers = ["0", "2"]
# 按照 市或区统计
groups = ["com_id", "sale_center_id"]
joins = [city, county]
#除需要计算的值，其他的数据
cols_comm = [["city", "brand_id", "brand_name", "ciga_data_marker"],
[
"county", "sale_center_id", "brand_id", "brand_name",
"ciga_data_marker"
]]
#需要计算的值的列名
cols = [["brand_city_orders", "brand_city_order_amount"],
["brand_county_orders", "brand_county_order_amount"]]
# 1.获取本月截止订单行表数据
co_co_line = get_co_co_line(spark, scope=[0, 0], filter="month")\
.join(plm_item, "item_id") \
.withColumn("brand_name", item_name_udf(col("item_name"))) \
.select("qty_ord", "amt", "brand_name", "com_id","sale_center_id")
co_co_line.cache()
for i in range(len(groups)):
group = groups[i]
join = joins[i]
c = cols[i]
marker = markers[i]
try:
print(f"{str(dt.now())} 本月{group}各品牌的订单量，订单额")
#2.本月每款烟在每个区的订单量,订单额
qty_amt=co_co_line.groupBy([group,"brand_name"])\
.agg(f.sum(col("qty_ord")).alias(c[0]),f.sum(col("amt")).alias(c[1]))
columns = cols_comm[i] + c
qty_amt.join(brand, "brand_name") \
.withColumn("row", f.concat_ws("_", col(group), col("brand_id"))) \
.join(join, group) \
.withColumn("ciga_data_marker", f.lit(marker)) \
.foreachPartition(lambda x: write_hbase1(x, columns, hbase))
except Exception:
tb.print_exc()
co_co_line.unpersist()
except Exception:
tb.print_exc()
def get_brand_stats_info_weekly():
# 市/区每款卷烟上一周销量/环比/同比
try:
# 烟品牌id，烟品牌名称
brand = get_phoenix_table(spark, brand_table)
# 烟id，烟名称
plm_item = get_plm_item(spark).select("item_id", "item_name")
area = get_area(spark)
# com_id与city的映射关系
city = area.dropDuplicates(["com_id"]).select("com_id", "city")
# sale_center_id与区(list)的映射关系
county = area.groupBy("sale_center_id") \
.agg(f.collect_list("county").alias("county")) \
.select("sale_center_id", "county")
#标识列的值
markers = ["0", "2"]
# 按照 市或区统计
groups = ["com_id", "sale_center_id"]
joins = [city, county]
# 除需要计算的值，其他的数据
cols_comm = [["city", "brand_id", "brand_name", "ciga_data_marker"],
[
"county", "sale_center_id", "brand_id", "brand_name",
"ciga_data_marker"
]]
# 需要计算的值的列名
cols = [[
"brand_city_week_sales", "brand_city_week2_sales",
"brand_city_week_sales_last_year"
],
[
"brand_county_week_sales", "brand_county_week2_sales",
"brand_county_week_sales_last_year"
]]
co_co_line = get_co_co_line(spark, scope=[1, 2], filter="week") \
.join(plm_item, "item_id") \
.withColumn("brand_name", item_name_udf(col("item_name"))) \
.select("brand_name", "com_id", "sale_center_id", "qty_ord", "week_diff")
co_co_line.cache()
last_year = spark.sql("select * from DB2_DB2INST1_CO_CO_LINE") \
.where(col("com_id").isin(cities)) \
.withColumn("born_date", f.to_date("born_date", "yyyyMMdd")) \
.withColumn("last_year_today", f.date_sub(f.current_date(), 365)) \
.withColumn("week_diff", week_diff("last_year_today", "born_date")) \
.where(col("week_diff") == 1) \
.withColumn("qty_ord", col("qty_ord").cast("float")) \
.join(plm_item, "item_id") \
.withColumn("brand_name", item_name_udf(col("item_name"))) \
.select("brand_name", "com_id","sale_center_id", "qty_ord")
last_year.cache()
for i in range(len(groups)):
group = groups[i]
join = joins[i]
c = cols[i]
marker = markers[i]
try:
# 获取上一周订单行表数据
last_week = co_co_line.where(col("week_diff") == 1)
# 1.市/区每款烟的订单量
print(f"{str(dt.now())} 上一周{group}每个品牌的销量")
qty_amt = last_week.groupBy(group, "brand_name").agg(
f.sum(col("qty_ord")).alias(c[0]))
# -------------------环比
# 获取上第二周订单行表数据
last_two_week = co_co_line.where(col("week_diff") == 2)
# 2.市/区每款卷烟销量 上次同期
print(f"{str(dt.now())} 上第二周{group}每个品牌的销量")
qty_amt_last = last_two_week.groupBy(group, "brand_name").agg(
f.sum(col("qty_ord")).alias(c[1]))
# # 2.市/区每款卷烟销量 环比
# print(f"{str(dt.now())} 上一周{group}每个品牌的销量环比")
# qty_amt_last.join(qty_amt, [group, "brand"]) \
# .withColumn("qty_ring_rate", period_udf(col("qty_ord"), col("qty_ord_last")))
# --------------------同比
# 3.市/区每款烟的订单量 去年
print(f"{str(dt.now())} 去年同期{group}每个品牌的销量")
qty_ord_ly = last_year.groupBy(group, "brand_name") \
.agg(f.sum(col("qty_ord")).alias(c[2]))
# # 3.市/区每款卷烟销量同比
# print(f"{str(dt.now())} 上一周{group}每个品牌的销量的同比")
# qty_ord_ly.join(qty_amt, [group, "brand"]) \
# .withColumn("qty_ord_yoy", period_udf(col("qty_ord"), col("qty_ord_ly")))
all_df=qty_amt.join(qty_amt_last,[group,"brand_name"],"outer")\
.join(qty_ord_ly,[group,"brand_name"],"outer") \
.na.fill(0, c)
columns = cols_comm[i] + c
all_df.join(brand, "brand_name") \
.withColumn("row", f.concat_ws("_", col(group), col("brand_id"))) \
.join(join, group) \
.withColumn("ciga_data_marker", f.lit(marker)) \
.foreachPartition(lambda x: write_hbase1(x, columns, hbase))
except Exception:
tb.print_exc()
co_co_line.unpersist()
last_year.unpersist()
except Exception:
tb.print_exc()