hive/postgresql日期时间范围查询数据，没有的数据补0

---临时日历表：calendar --涉及时间从2016-2043年，主要用于按时间维度补0操作   
CREATE TABLE num (i int);   
---创建一个表用来储存0-9的数字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
--- 生成0-9的数字，方便以后计算时间
drop table if exists calendar;
CREATE TABLE  if not exists calendar(datelist date); 
INSERT INTO calendar(datelist) 
SELECT    
  to_date('2016-1-1'::text,'yyyy-mm-dd')+cast(numlist.id||'days' as interval) AS "date"
FROM(    
  SELECT        
    n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 AS id    
  FROM        
    num n1    
  CROSS JOIN num AS n10    
  CROSS JOIN num AS n100    
  CROSS JOIN num AS n1000
  )
  AS numlist;
---cj_year  临时时间adcode表(主要用于根据adcode查询对应时间的影像和(补0操作))
 create table cj_year(datelist date,adcode VARCHAR(20));
 INSERT into cj_year(datelist,adcode)
 SELECT    
   year,o.adcode
 from  
   satellite_productlevel_year
 CROSS JOIN
 (select       
      adcode   
  from district
  )o 
  where year is not null
---临时产品等级adcode表：cj_product_level
---主要用于根据adcode查询对应产品等级的影像和(补0操作)
  create table cj_product_level(product_level varchar(20),adcode VARCHAR(20))
  INSERT into cj_product_level(product_level,adcode)
    SELECT    
      product_level,o.adcode
    from  
      satellite_productlevel_year 
    CROSS JOIN(   
    select       
      adcode   
    from district)o
    where product_level is not null

---过去一个星期每日影像下载数量---
def NumberOfTiffDownWeek(spark: SparkSession): Unit = {
  val dataFrame: DataFrame = spark.sql(
    """
      |SELECT
      |    date(dday) time,
      |    sum(ct) as num
      |FROM
      |    (
      |        SELECT
      |            datelist as dday,
      |            0 as ct
      |        FROM
      |            calendar
      |   where datelist>date_sub(current_timestamp,7) and datelist<current_timestamp
      |    UNION ALL
      |   select
      |    date_format(created_at,'yyyy-MM-dd') as dday,
      |    count(1) as ct
      |   from products
      |   where  datediff(current_timestamp, created_at)<7
      |   group by date_format(created_at,'yyyy-MM-dd')
      |  ) a
      |GROUP BY time
      |order by time
      |""".stripMargin)
  val transforDF =dataFrame.select(dataFrame.col("time").cast(StringType),dataFrame.col("num"))
  dataFrame2Json(transforDF, "过去一个星期每日影像下载数量")
  println("过去一个星期每日影像下载数量已更新至postgres")
}
---过去一个月每日影像入库景数---
def TotalNumTiffEveryDay(spark: SparkSession): Unit = {
  val dataFrame: DataFrame = spark.sql(
    """
      |SELECT
      |    date(dday) time,
      |    sum(ct) num
      |FROM
      |    (
      |        SELECT
      |            datelist as dday,
      |           0 as ct
      |        FROM
      |            calendar
      |   where datelist>date_sub(current_timestamp,30) and datelist<current_timestamp
      |    UNION ALL
      |   select
      |    date_format(storage_at,'yyyy-MM-dd') dday
      |    ,count(1) ct
      |   from image_package
      |   where datediff(current_timestamp, storage_at)<30
      |   GROUP by date_format(storage_at,'yyyy-MM-dd')
      |  ) a
      |GROUP BY time
      |order by time
      |""".stripMargin)
  val transforDF: DataFrame = dataFrame.select(dataFrame.col("time").cast(StringType),dataFrame.col("num"))
  dataFrame2Json(transforDF,"过去一个月每日影像入库景数")
  println("过去一个月每日影像入库景数已更新至postgres")
}