dwd_st_asin_measure_old.py 14.3 KB
Newer Older
chenyuanjie committed
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 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300
import os
import sys

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
from pyspark.storagelevel import StorageLevel
from utils.templates import Templates
# from ..utils.templates import Templates
# from AmazonSpider.pyspark_job.utils.templates_test import Templates
from pyspark.sql.types import StringType, IntegerType
# 分组排序的udf窗口函数
from pyspark.sql.window import Window
from pyspark.sql import functions as F


class DwdStMeasure(Templates):

    def __init__(self, site_name='us', date_type="month", date_info='2022-1'):
        super().__init__()
        self.site_name = site_name
        self.date_type = date_type
        self.date_info = date_info
        self.db_save_st_asin = f'dwd_st_asin_measure'
        self.db_save_st = f'dwd_st_measure'
        self.db_save_asin = f'dwd_asin_measure'
        self.spark = self.create_spark_object(
            app_name=f"{self.db_save_st_asin}, {self.db_save_st}, {self.db_save_asin}: {self.site_name}, {self.date_type}, {self.date_info}")
        # self.df_date = self.get_year_week_tuple()  # pandas的df对象
        self.get_date_info_tuple()
        self.df_st_detail = self.spark.sql(f"select 1+1;")
        self.df_st_asin = self.spark.sql(f"select 1+1;")
        self.df_st_asin_flow = self.spark.sql(f"select 1+1;")
        self.df_st_rate = self.spark.sql(f"select 1+1;")
        self.df_asin_history = self.spark.sql(f"select 1+1;")
        self.df_bs_report = self.spark.sql(f"select 1+1;")
        self.df_st_quantity = self.spark.sql(f"select 1+1;")
        self.df_st_asin_duplicated = self.spark.sql(f"select 1+1;")
        self.df_save_st_asin = self.spark.sql(f"select 1+1;")
        self.df_save_asin = self.spark.sql(f"select 1+1;")
        self.df_save_st = self.spark.sql(f"select 1+1;")
        self.partitions_num = 3
        self.reset_partitions(partitions_num=self.partitions_num)
        self.partitions_by = ['site_name', 'date_type', 'date_info']

    def read_data(self):
        print("1.1 读取dim_st_detail表")
        sql = f"select search_term, st_rank from dim_st_detail where site_name='{self.site_name}' and date_type='{self.date_type}' and date_info in {self.date_info_tuple}"
        print("sql:", sql)
        self.df_st_detail = self.spark.sql(sqlQuery=sql).cache()
        # self.df_st_detail.show(10, truncate=False)
        print("1.1 读取dim_st_asin_info表")
        sql = f"select * from dim_st_asin_info where site_name='{self.site_name}' and date_type='day' and date_info in {self.date_info_tuple}"
        print("sql:", sql)
        self.df_st_asin = self.spark.sql(sqlQuery=sql).cache()
        self.df_st_asin = self.df_st_asin.drop_duplicates(["search_term", "asin", "data_type", "date_info"]).cache()
        self.df_st_asin_duplicated = self.df_st_asin.drop_duplicates(['search_term', 'asin']).cache()
        print("self.df_st_asin:", self.df_st_asin.count())
        print("self.df_st_asin_duplicated:", self.df_st_asin_duplicated.count())
        # self.df_st_asin.show(10, truncate=False)
        # self.df_asin = self.df_st_asin.select("asin").drop_duplicates(["asin"])
        # self.df_st = self.df_st_asin.select("search_term").drop_duplicates(["search_term"])
        print("1.2 读取ods_rank_flow表")
        sql = f"select rank as st_asin_zr_page_rank, rank as st_asin_sp_page_rank, flow as st_asin_zr_rate, flow as st_asin_sp_rate from ods_rank_flow " \
              f"where site_name='{self.site_name}'"
        self.df_st_asin_flow = self.spark.sql(sql).cache()
        # self.df_st_asin_flow.show(10, truncate=False)
        print("1.3 读取ods_rank_search_rate_repeat表")
        sql = f"select rank as st_rank, search_num as st_search_num, rate as st_search_rate, search_sum as st_search_sum " \
              f"from ods_rank_search_rate_repeat where site_name='{self.site_name}';"
        self.df_st_rate = self.spark.sql(sql).cache()
        # self.df_st_rate.show(10)
        # 1.4 获取asin的bs_id, 卖家, 店铺等
        print("1.4 读取dim_cal_asin_history_detail表")
        sql = f"select asin, asin_rank, bsr_cate_1_id, asin_title " \
              f"from dim_cal_asin_history_detail where site_name='{self.site_name}';"
        self.df_asin_history = self.spark.sql(sql).cache()
        # self.df_asin_history.show(10)
        # 1.5 ods_one_category_report
        print("1.5 读取ods_one_category_report表")
        sql = f"select cate_1_id as bsr_cate_1_id, rank as asin_rank, orders as asin_bsr_orders from ods_one_category_report " \
              f"where site_name='{self.site_name}' and dm='2022-11';"
        self.df_bs_report = self.spark.sql(sqlQuery=sql).cache()
        # self.df_bs_report.show(10, truncate=False)
        # 1.6 ods_st_quantity_being_sold
        print("1.6 读取ods_st_quantity_being_sold表")
        sql = f"select search_term, quantity_being_sold as st_quantity_being_sold from ods_st_quantity_being_sold " \
              f"where site_name='{self.site_name}'  and date_type='{self.date_type}' and date_info in {self.date_info_tuple};"
        self.df_st_quantity = self.spark.sql(sqlQuery=sql).cache()

    def save_data(self):
        self.reset_partitions(partitions_num=50)
        self.save_data_common(
            df_save=self.df_save_st_asin,
            db_save=self.db_save_st_asin,
            partitions_num=self.partitions_num,
            partitions_by=self.partitions_by
        )
        self.reset_partitions(partitions_num=10)
        self.save_data_common(
            df_save=self.df_save_st,
            db_save=self.db_save_st,
            partitions_num=self.partitions_num,
            partitions_by=self.partitions_by
        )
        self.reset_partitions(partitions_num=10)
        self.save_data_common(
            df_save=self.df_save_asin,
            db_save=self.db_save_asin,
            partitions_num=self.partitions_num,
            partitions_by=self.partitions_by
        )

    def handle_st_zr_page1_title_rate(self):
        df_zr_page1 = self.df_st_asin.filter(
            "data_type='zr' and page=1"
        )
        df_zr_page1 = df_zr_page1.drop_duplicates(["search_term", "asin"])
        df_zr_page1 = df_zr_page1.join(
            self.df_asin_history
        )

    def handle_data(self):
        self.handle_st_info()
        self.handle_st_asin_info()
        self.df_save_asin = self.handle_st_asin_counts(cal_type="asin")
        self.df_save_st = self.handle_st_asin_counts(cal_type="st")
        self.df_save_st = self.df_save_st.join(
            self.df_st_detail, on=['search_term'], how='left'
        )
        self.handle_st_asin_ao()
        self.handle_st_asin_orders()
        self.handle_st_asin_bsr_orders()
        self.df_save_st_asin.show(10, truncate=False)
        self.df_save_st.show(10, truncate=False)
        self.df_save_asin.show(10, truncate=False)
        quit()

    def handle_st_info(self):
        # 处理在售商品数
        self.df_st_quantity = self.df_st_quantity.filter("st_quantity_being_sold > 0").groupby(['search_term']).agg(
            {"st_quantity_being_sold": "mean"}
        )
        self.df_st_quantity = self.df_st_quantity.withColumnRenamed(
            "avg(st_quantity_being_sold)", "st_quantity_being_sold"
        )
        self.df_st_detail = self.df_st_detail.join(
            self.df_st_quantity, on=["search_term"], how="left"
        ).join(
            self.df_st_rate, on=["st_rank"], how="left"
        )

    def handle_st_asin_info(self):
        # self.df_save_st_asin = self.df_st_asin.filter("data_type in ('zr', 'sp')").withColumn(
        self.df_save_st_asin = self.df_st_asin.withColumn(
            "page_rank_data_type", F.concat(F.lit("st_asin_"), self.df_st_asin.data_type, F.lit("_page_rank"))
        )
        self.df_save_st_asin = self.df_save_st_asin.groupby(["search_term", "asin"]). \
            pivot("page_rank_data_type").agg(F.min(f"page_rank"))
        self.df_save_st_asin = self.df_save_st_asin. \
            join(self.df_st_asin_flow.select("st_asin_zr_page_rank", "st_asin_zr_rate"), on=["st_asin_zr_page_rank"],
                 how="left"). \
            join(self.df_st_asin_flow.select("st_asin_sp_page_rank", "st_asin_sp_rate"), on=["st_asin_sp_page_rank"],
                 how="left"). \
            join(self.df_st_detail, on=["search_term"], how="inner")
        # self.df_save_st_asin.show(10, truncate=False)

    def handle_st_asin_counts(self, cal_type="asin"):
        print(f"计算{cal_type}_counts")
        cal_type_complete = "search_term" if cal_type == "st" else cal_type
        self.df_st_asin = self.df_st_asin.withColumn(
            f"{cal_type}_data_type",
            F.concat(F.lit(f"{cal_type}_"), self.df_st_asin.data_type, F.lit(f"_counts"))
        )
        df = self.df_st_asin.groupby([f'{cal_type_complete}']). \
            pivot(f"{cal_type}_data_type").count()
        df = df.fillna(0)
        # df.show(10, truncate=False)
        df = df.withColumn(
            f"{cal_type}_sb_counts",
            df[f"{cal_type}_sb1_counts"] + df[f"{cal_type}_sb2_counts"] + df[f"{cal_type}_sb3_counts"]
        )
        df = df.withColumn(
            f"{cal_type}_adv_counts",
            df[f"{cal_type}_sb_counts"] + df[f"{cal_type}_sp_counts"]
        )
        df = df.withColumn(f"site_name", F.lit(self.site_name))
        df = df.withColumn(f"date_type", F.lit(self.date_type))
        df = df.withColumn(f"date_info", F.lit(self.date_info))
        # df.show(10, truncate=False)
        return df

    def handle_st_asin_ao(self):
        print("计算st和asin各自维度的ao")
        # asin_ao_val
        self.df_save_asin = self.df_save_asin.withColumn(
            "asin_ao_val", self.df_save_asin.asin_adv_counts / self.df_save_asin.asin_zr_counts
        )
        self.df_save_asin = self.df_save_asin.fillna({"asin_ao_val": 0})
        # st_ao_val和st_ao_val_rate
        df_asin_ao = self.df_save_asin.select("asin", "asin_ao_val")
        df_st_ao = self.df_save_st_asin.select("search_term", "asin").join(
            df_asin_ao, on=['asin'], how='left'
        )
        df_st_ao = df_st_ao.groupby(["search_term"]).agg({"asin_ao_val": "mean"})
        df_st_ao = df_st_ao.withColumnRenamed("avg(asin_ao_val)", "st_ao_val")
        self.df_save_st = self.df_save_st.join(
            df_st_ao, on=['search_term'], how='left'
        )
        window = Window.orderBy(self.df_save_st.st_ao_val.asc())
        self.df_save_st = self.df_save_st.withColumn("st_ao_val_rate", F.percent_rank().over(window=window))

    def handle_st_asin_bsr_orders(self):
        self.df_save_st_asin = self.df_save_st_asin.join(
            self.df_asin_history, on=['asin'], how='left'
        )
        self.df_save_st_asin = self.df_save_st_asin.join(
            self.df_bs_report, on=['asin_rank', 'bsr_cate_1_id'], how='left'
        )
        # self.df_st_asin_duplicated.show(10, truncate=False)
        df_st_bsr_orders = self.df_save_st_asin.groupby(['search_term']).agg({"asin_bsr_orders": "sum"})
        df_st_bsr_orders = df_st_bsr_orders.withColumnRenamed(
            "sum(asin_bsr_orders)", "st_bsr_orders"
        )
        df_asin_bsr_orders = self.df_save_st_asin.select("asin", "asin_bsr_orders").drop_duplicates(['asin'])
        # df_st_bsr_orders.show(10, truncate=False)
        # df_asin_bsr_orders.show(10, truncate=False)
        self.df_save_st = self.df_save_st.join(
            df_st_bsr_orders, on='search_term', how='left'
        )
        self.df_save_asin = self.df_save_asin.join(
            df_asin_bsr_orders, on='asin', how='left'
        )

    def handle_st_asin_orders(self):
        print("计算zr, sp预估销量")
        # 1. st+asin维度的zr和sp预估销量
        self.df_save_st_asin = self.df_save_st_asin.withColumn(
            "st_asin_zr_orders",
            F.ceil(self.df_save_st_asin.st_asin_zr_rate * self.df_save_st_asin.st_search_sum)
        ).withColumn(
            "st_asin_sp_orders",
            F.ceil(self.df_save_st_asin.st_asin_sp_rate * self.df_save_st_asin.st_search_sum)
        )
        # self.df_save_st_asin.show(10, truncate=False)
        self.df_save_st_asin = self.df_save_st_asin.select(
            "search_term", "asin", "st_asin_zr_orders", "st_asin_sp_orders",
        )
        self.df_save_st_asin = self.df_save_st_asin.groupby(["search_term", "asin"]).agg(
            {
                "st_asin_zr_orders": "mean",
                "st_asin_sp_orders": "mean",
            }
        )
        self.df_save_st_asin = self.df_save_st_asin.withColumnRenamed(
            "avg(st_asin_zr_orders)", "st_asin_zr_orders"
        ).withColumnRenamed(
            "avg(st_asin_sp_orders)", "st_asin_sp_orders"
        )
        self.df_save_st_asin = self.df_save_st_asin.withColumn(f"site_name", F.lit(self.site_name))
        self.df_save_st_asin = self.df_save_st_asin.withColumn(f"date_type", F.lit(self.date_type))
        self.df_save_st_asin = self.df_save_st_asin.withColumn(f"date_info", F.lit(self.date_info))
        print("self.df_save_st_asin:", self.df_save_st_asin.count())
        # self.df_save_st_asin.show(10, truncate=False)
        # 2. st维度的zr和sp预估销量
        df_st_orders = self.df_save_st_asin.groupby(['search_term']).agg(
            {
                "st_asin_zr_orders": "sum",
                "st_asin_sp_orders": "sum",
            }
        )
        df_st_orders = df_st_orders.withColumnRenamed(
            "sum(st_asin_zr_orders)", "st_zr_orders"
        ).withColumnRenamed(
            "sum(st_asin_sp_orders)", "st_sp_orders"
        )
        self.df_save_st = self.df_save_st.join(
            df_st_orders, on=['search_term'], how='left'
        )
        # 3. asin维度的zr和sp预估销量
        df_asin_orders = self.df_save_st_asin.groupby(['asin']).agg(
            {
                "st_asin_zr_orders": "mean",
                "st_asin_sp_orders": "mean",
            }
        )
        df_asin_orders = df_asin_orders.withColumnRenamed(
            "avg(st_asin_zr_orders)", "asin_zr_orders"
        ).withColumnRenamed(
            "avg(st_asin_sp_orders)", "asin_sp_orders"
        )
        self.df_save_asin = self.df_save_asin.join(
            df_asin_orders, on=['asin'], how='left'
        )


if __name__ == '__main__':
    site_name = sys.argv[1]  # 参数1:站点
    date_type = sys.argv[2]  # 参数2:类型:day/week/4_week/month/quarter
    date_info = sys.argv[3]  # 参数3:年-月-日/年-周/年-月/年-季, 比如: 2022-1
    handle_obj = DwdStMeasure(site_name=site_name, date_type=date_type, date_info=date_info)
    handle_obj.run()