Hya-sql-memo

sql_pred = """ select k.record_time, k.predict_datetime, k.object_code, sum(k.predict_value) AS area_pred_qty from (select record_time, predict_datetime, object_code, predict_value, product_type from dm_predict.region_predict_result where task_type = 'pickup' and substr(record_time, 1, 10) = '2021-09-27' and predict_datetime in ('2021-10-01','2021-10-02')) k group by record_time,predict_datetime,object_code """ DF_pred = spark.sql(sql_pred) DF_pred.createOrReplaceTempView("DF_pred_qty") '''select k.area_code,k.type_date,k.type,k.income_code, sum(k.qty) AS area_pred_qty from (select object_code as area_code, predict_datetime as type_date, task_type as type, product_type as income_code, predict_value as qty, predict_period from dm_predict.region_predict_result where inc_day in ('20211101', '20211102', '20211103', '20211104', '20211105', '20211106', '20211107', '20211108', '20211109', '20211110', '20211111', '20211112', '20211113', '20211114', '20211115', '20211116', '20211117', '20211118', '20211119', '20211120', '20211121', '20211122', '20211123', '20211124', '20211125', '20211126', '20211127', '20211128', '20211129', '20211025', '20211026', '20211027', '20211028', '20211029', '20211030') and predict_period = '1D') k group by k.area_code,k.type_date,k.type,k.income_code''' #----------------------------------------------------------------------------------------------------------------- sql_real = """ select k.days, k.area_code, sum(k.all_quantity) area_real_qty_yesterday from ( select income_code, area_code, all_quantity, days from dm_ordi_predict.static_area_org where inc_day <= '20210927' and inc_day >= '20210901' and type = 'pickup' ) k group by days, area_code """ DF_real = spark.sql(sql_real) DF_real.createOrReplaceTempView("DF_real_qty") sql_real = """ select k.days, k.area_code, sum(k.all_quantity) area_real_qty_yesterday from ( select income_code, area_code, all_quantity, days from dm_ordi_predict.static_area_org where inc_day <= '{0}' and inc_day >= '{1}' and type = 'pickup' ) k group by days, area_code """.format(today,before) # 区分产品板块，收/派的版本 ############################# '''select k.days, k.area_code, k.income_code, k.type, sum(k.all_quantity) area_real_qty from ( select income_code, type, all_quantity, days, area_code from dm_ordi_predict.static_area_org where inc_day <= '20201126' and inc_day >= '20201026' ) k group by income_code, type, days, area_code ''' #----------------------------------------------------------------------------------------------------------------- sql_batch_qty = """ select actual_arrive_votes as batch_real_qty, batch_code, inc_day FROM dm_ops.pass_forecast_batch_monitor_dtl where inc_day between '20210901' and '20210927' """ DF_batch_qty = spark.sql(sql_batch_qty) DF_batch_qty.createOrReplaceTempView("DF_batch_qty") # 检查数据是否全 '''select count(*) as a from ( select distinct inc_day from ( select actual_arrive_votes as batch_real_qty, batch_code, inc_day FROM dm_ops.pass_forecast_batch_monitor_dtl where inc_day <= '20201121' and inc_day >= '20201030' ) g ) d'''