st_rank.py

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-01'):
        super().__init__()
        self.site_name = site_name
        self.date_type = date_type
        self.date_info = date_info
        self.db_save = f'dwd_st_rank'
        self.spark = self.create_spark_object(
            app_name=f"{self.db_save}: {self.site_name}, {self.date_type}, {self.date_info}")
        # self.df_date = self.get_year_week_tuple()  # pandas的df对象
        self.df_st_search = self.spark.sql(f"select 1+1;")
        self.df_st_current_year = self.spark.sql(f"select 1+1;")
        self.df_st_last_year = self.spark.sql(f"select 1+1;")
        self.df_save = self.spark.sql(f"select 1+1;")

    def read_data(self):
        sql = f"select rank as st_rank_2023, search_num as st_search_num_2023 from ods_rank_search_rate_repeat " \
              f"where site_name='{self.site_name}' and date_type='month' and date_info='2022-12'"
        self.df_st_search = self.spark.sql(sqlQuery=sql).cache()
        self.df_st_search.show(10, truncate=False)
        sql = f"select search_term, st_rank as st_rank_2023 from dim_st_detail " \
              f"where site_name='{self.site_name}' and date_type='month' and date_info in ('2023-01')"
        self.df_st_current_year = self.spark.sql(sqlQuery=sql).cache()
        self.df_st_current_year.show(10, truncate=False)
        sql = f"select search_term, st_rank as st_rank_2022, st_search_num as st_search_num_2022 from dim_st_detail " \
              f"where site_name='{self.site_name}' and date_type='month' and date_info in ('2022-01')"
        self.df_st_last_year = self.spark.sql(sqlQuery=sql).cache()
        self.df_st_last_year.show(10, truncate=False)

        self.df_st_current_year = self.df_st_current_year.groupby(['search_term']).agg({
            "st_rank_2023": "mean",
        })
        self.df_st_current_year.show(10, truncate=False)
        self.df_st_current_year = self.df_st_current_year.withColumnRenamed("avg(st_rank_2023)", "st_rank_2023")
        window = Window.orderBy(
            self.df_st_current_year.st_rank_2023.asc()
        )
        self.df_st_current_year = self.df_st_current_year.withColumn("st_rank_2023", F.row_number().over(window=window))
        self.df_st_current_year = self.df_st_current_year.join(
            self.df_st_search, on='st_rank_2023', how='left'
        )
        self.df_st_current_year.show(10, truncate=False)

        self.df_save = self.df_st_current_year.join(
            self.df_st_last_year, on='search_term', how='left'
        )
        self.df_save.show(10, truncate=False)
        self.df_save = self.df_save.withColumn(
            "st_search_rate",
            (self.df_save.st_search_num_2023 - self.df_save.st_search_num_2022) / self.df_save.st_search_num_2023
        ).withColumn(
            "st_rank_rate",
            (self.df_save.st_rank_2023 - self.df_save.st_rank_2022) / self.df_save.st_rank_2023
        )
        self.df_save = self.df_save.sort(['st_rank_2023'])
        print("self.df_save:", self.df_save.count())
        self.df_save.show(10, truncate=False)
        df_st_70w = self.df_save.filter("st_rank_2023<=1000000 and st_rank_2022 is null")
        print("df_st_70w:", df_st_70w.count())
        df_st_70w.show(10, truncate=False)
        df_save = self.df_save.toPandas()
        df_save.to_csv("/opt/module/spark-3.2.0-bin-hadoop3.2/demo/py_demo/temp/同比增幅.csv", index=False)
        df_st_70w = df_st_70w.toPandas()
        df_st_70w.to_csv("/opt/module/spark-3.2.0-bin-hadoop3.2/demo/py_demo/temp/前70w新出现.csv", index=False)
        quit()


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()