[SQL/Java] 数据膨胀和时间格式转换

今天在工作中遇到两个问题

1. 两张hive表关联时，由于a表存在重复数据，造成另外一张b表的数据膨胀。

2. Java的时间格式转换函数的使用

一、关联的数据膨胀

在计算广告的点击率预估中，需要通过点击表关联曝光表，从而生成label表。数据膨胀就发生在关联的过程中，代码如下：

-- sql 1
select case when (click.imei is not null and click.id is not null and click.url is not null) then 1 else 0 end as label,
       case when (click.imei is not null and click.id is not null and click.url is not null) then click.id else expo.id end as id,
       case when (click.imei is not null and click.id is not null and click.url is not null) then click.imei else expo.imei end as imei,
       case when (click.imei is not null and click.id is not null and click.url is not null) then unix_timestamp(substr(click.dt, 1, 19)) else unix_timestamp(substr(expo.dt, 1, 19)) end as dt
from
(select imei, id, url, dt
from ${clickTable}
where day = '${day}'
and imei is not null
and id is not null
and url is not null
) click

full outer join
(select imei, id, url, dt
from ${expoTable}
where day = '${day}'
and imei is not null
and id} is not null
and url is not null
) expo
on click.imei = expo.imei and click.id = expo.id and click.url = expo.url

代码思路为：用点击表full join曝光表，用1表示点击样本，0表示曝光样本。只要点击表的字段不为null，即存在点击，该条样本即标识为1；反之，如果点击表字段为null，即不存在点击，则该条样本标识为0。（注意这里：可能存在有点击却没有对应曝光的数据，此种可能为曝光数据漏上报，也应该标示为点击样本）

sql-1发生数据膨胀的原因在于：在full join的过程中，对于点击表中的一条数据，在曝光表中可能存在多条，因此按照imei，id，url三者关联后，点击数据会发生膨胀。（此处更多的是对业务的理解，即数据中是否存在多条重复数据，需要事先检查）。

改进后的代码如sql 2

--sql 2
select 1 as label,
       id,
       imei,
       unix_timestamp(substr(dt, 1, 19)) as dt
from ${clickTable}
where day = '${day}'
and imei is not null
and id is not null
and url is not null

union all

select 0 as label,
       expo.id as id,
       expo.imei as imei,
       unix_timestamp(substr(expo.dt, 1, 19)) as dt
from
(select imei, id, url, dt} dt
from ${expoTable}
where day = '${day}'
and imei is not null
and id is not null
and url is not null
) expo

left join
(select imei, id, url, dt} dt
from ${clickTable}
where day = '${day}'
and imei is not null
and id is not null
and url is not null
) click
on click.imei = expo.imei and click.id = expo.id and click.url = expo.url
where click.imei is null and click.id is null and click.url is null

代码思路为：分别选出点击样本和曝光样本，再进行合并。对于点击样本，凡是所有存在于点击表中的数据，都标识为1。对于曝光样本，用曝光数据和点击数据做差集（即有曝光无点击）的结果即为曝光样本，标识为0。

此代码为何正确：对于下载样本，由于没有进行表的关联，肯定不存在膨胀；对于曝光样，因为用曝光表left join下载表，下载表中没有重复数据（每条数据有且仅有一份），因此不会造成曝光样本的膨胀。

二、java的时间格式转换

目的：将yyyyMMdd格式的日期转换为yyyy-MM-dd，例如20200915转化为2020-09-15

// 错误
def getDay(date: String): String = {
  val sdf = new SimpleDateFormat("yyyy-MM-dd")
    sdf.format(sdf.parse(date))
}

错误，原因在于，sdf.parse(date)无法解析，因为输入的日期格式和sdf指定的日期格式不匹配，无法解析。

修正为如下：即先用yyyyMMdd的sdf完成日期的解析，然后修改sdf的模式为yyyy-MM-dd，再进行重新的格式化。

• 方法一

  // 正确
  def getDay(date: String): String = {
    val sdf = new SimpleDateFormat("yyyyMMdd");
    val d = sdf.parse(date);
    sdf.applyPattern("yyyy-MM-dd");
    sdf.format(d)
  }
  

• 方法二

  // 正确
  def dateFormatConvert(date: String): String = {
      val sdf1 = new SimpleDateFormat("yyyyMMdd")
      val sdf2 = new SimpleDateFormat("yyyy-MM-dd")
      val newDate = sdf2.format(sdf1.parse(date))
      newDate
  }