57. [SQL]业务数据库中的create_time和update_time分析时的问题

一些业务数据库(比如MySQL)中的表结构设计为了表示数据的创建(插入)和更新时间，会有两个字段表示，分别是create_time和update_time，前者表示该条数据是何时创建或者插入到业务数据库的，后者表示该条数据是何时发生了最近一次更新。

在数仓设计中，明细层(DWD)或者汇总层(DWS)由于底层数据冗余的问题，比如明细层一个用户发省了多次行为，数据虽然发省了更新，但是明细层并不是全量计算，而是增量计算(表为非分区表，新数据每天插入)，这时，在汇总层做一些状态取值需要取最新的时候，就需要根据如题所说的两个字段来对状态进行合适的取舍计算，情况大致会有以下几种。

57.1. 1、根据update_time取最新

这时分析函数主要使用ROW_NUMBER() OVER()

ROW_NUMBER() OEVER(PARTITION BY id ORDER BY update_time DESC)

以上则表示以id分区，update_time倒序排序，将排在第一位的标号为1，在外层筛选数据时只要where rank=1，即刻取到最新的一条。

可是这样的取法存在一个问题，如果出现两个update_time相同，但是create_time不同的数据时，这时候执行结果就不一定了，而业务库中发生这样的数据是很有可能的(插入新的数据的同时将旧的状态数据一并更新)，往往结果是随机的，结果就不能确保是我们想要的。

57.2. 2、根据update_time和create_time组合取最新

1中存在的问题，并不难解决，这时候只要稍微改动SQL即可：

ROW_NUMBER() OEVER(PARTITION BY id ORDER BY update_time DESC, create_time DESC)

以上则会根据update_time倒序排序后，再根据已经排序过的根据create_time进行倒序排序，确保取到我们最新的状态。

57.3. 3、根据create_time取最新

还有这样一种业务情况存在，create_time表示了该条数据的创建(插入)时间，如果再发生需要对该数据进行更新并插入新的数据的情况，两条数据实则是一并更新的，就会有相同的update_time，但create_time一定不相同

ROW_NUMBER() OEVER(PARTITION BY id ORDER BY create_time DESC)

举例：

id name       status    hight  create_time           update_time
1  xiaoming   active    170    2021-09-13 12:00:01   2021-09-13 12:00:01
------------------------------------------------------------------------
id name       status    hight  create_time           update_time
1  xiaoming   active    170    2021-09-13 12:00:01   2021-09-15 10:04:01
1  xiaoming   active    177    2021-09-15 09:00:01   2021-09-15 10:04:01

以上的情况需要综合考虑，根据实际的需要进行取舍，上面的分析也就是提醒自己在以后主要到该种表的设计时，做分析的时候需要注意这几种情况。