mysql - 填写由列本身和日期确定的列中的缺失值

Question

我有一个订阅表，如下所示。大约有 100 万个唯一 ID。该表列出了用户开始订阅服务时的订阅状态(用“Sub”表示)和取消订阅时的订阅状态(用“Usub”表示)。

ID Year Month Status
A  2014   1    
A  2014   2    
A  2014   3    
A  2014   4    Sub
A  2014   5    
A  2014   6    
A  2014   7    
A  2014   8    Usub 
A  2014   9    
A  2014  10    
A  2014  11    
A  2014  12    
B  2014   1    
B  2014   2    
B  2014   3    
B  2014   4    
B  2014   5    Usub    
B  2014   6    
B  2014   7    
B  2014   8    
B  2014   9    Sub
B  2014  10    
B  2014  11    
B  2014  12    
.    .    .     .
.    .    .     .
.    .    .     .

我希望填补每次状态更新之间的空白。所需的输出表如下所示:

ID Year Month Status
A  2014   1    Usub
A  2014   2    Usub
A  2014   3    Usub
A  2014   4    Sub
A  2014   5    Sub
A  2014   6    Sub
A  2014   7    Sub
A  2014   8    Usub
A  2014   9    Usub
A  2014  10    Usub
A  2014  11    Usub
A  2014  12    Usub
B  2014   1    Sub
B  2014   2    Sub
B  2014   3    Sub
B  2014   4    Sub
B  2014   5    Usub
B  2014   6    Usub
B  2014   7    Usub
B  2014   8    Usub
B  2014   9    Sub
B  2014  10    Sub
B  2014  11    Sub
B  2014  12    Sub
.    .    .     .
.    .    .     .
.    .    .     .

虽然第一个表显示两个用户都有多个状态更改，但任何给定 ID 不一定有多个状态更改。但是，每个 ID 至少有一种状态。 IE。在 2014/8 订阅且从未取消订阅的用户将在 2014/8 拥有 status= Sub。

Answer 1

您可以使用过程代码来做到这一点，按顺序读取数据，并使用变量来存储前一条记录的“状态”(每个新 ID 都会重置)。
但是 SQL不是程序化的。

那么对于标准单节点数据库(例如 MySQL)和分布式系统(例如 Hive)，我们可以获得什么样的程序包装器？

• MySQL 有一种存储过程语言，支持游标迭代 SELECT 查询中的行(Google 将为您提供大量教程，例如随机获取的 this one)
• Hive 2.0还有一种procedural language extension ，但它是不是 Hive 本身的一部分——您必须在 specific tool (而且并不是每个人都拥有 Hive 2.0)——此外，Hive 在分布式文件系统上运行分布式查询，因此“洗牌”单个客户端节点上的所有行，然后使用单线程工具进行迭代听起来非常笨拙——请参阅您自己承担风险
• Hive 还有一种复杂而繁琐的方式来以分布式方式运行任意程序代码(在每个 Mapper 或每个Reducer 中)，使用 TRANSFORM 子句(Hive传统且名称不佳的“Hadoop 流”功能的翻译)；不幸的是the documentation is cryptic (你最好读一本好书，例如 O'Reilly 的《Programming Hive》)，当你最终可以让它工作时，性能下降是显着的
• 或者，您可以用 Java 开发一个自定义 Hive UDF，但需要稍加改动——即将前一行的状态存储在 Java 变量中并使用它对于当前行

现在，您需要在自定义查询和自定义代码中实现什么样的过程逻辑？

• 首先，您需要输入查询来 SORT BY key, date
• 使用 Hive TRANSFORM 或 Hive UDF，您还需要按键进行分发，以便每个键都在单个节点中将其历史记录作为一个整体进行处理(否则结果将是完全没有意义!)
• 为“PreviousRowKey”和“PreviousRowState”定义 2 个变量
• 在第一行或更改键时，则“PreviousRowState = Usub”和“PreviousRowKey = currentKey”
• 状态更改时，则“PreviousRowState = currentState”，否则强制“currentState = PreviousRowState”