sql填充空值
以下是所有与数据相关的技术中的一个非常普遍的问题，我们将针对这两个非常精简的基于SQL的解决方案进行研究：
如何用“先前的非空值”填充稀疏数据集的单元格？
问题
这个问题真的很简单， 在这个问题中我将重用Stack Overflow用户aljassi提供的示例 ：
我们有一个包含“稀疏”数据的表：
Col1 Col2 Col3 Col4----------------------A015B040C200D000E350F030G031H015I350
上面的数据集包含一些非零的有趣数据点，以及一些由零值建模的间隙。 在其他示例中，我们可以将零替换为NULL，但这仍然是相同的问题。 所需的结果如下：
Col1 Col2 Col3 Col4----------------------A015B045C245D245E355F335G331H315I355
请注意，所有生成的值都以红色突出显示，并且它们对应于最新的蓝色值。
如何使用SQL？ 我们将研究两种解决方案：
使用窗口函数的解决方案
这是您应该寻找的解决方案，并且在链接的堆栈溢出问题中有两个答案都使用了窗口函数：
用户nop77svk使用LAST_VALUE()的解决方案 用户MT0使用LAG()的解决方案
两种解决方案大致相同。 它们的工作方式如下（使用Oracle语法）：
WITH t(col1, col2, col3, col4) AS (SELECT 'A', 0, 1, 5 FROM DUAL UNION ALLSELECT 'B', 0, 4, 0 FROM DUAL UNION ALLSELECT 'C', 2, 0, 0 FROM DUAL UNION ALLSELECT 'D', 0, 0, 0 FROM DUAL UNION ALLSELECT 'E', 3, 5, 0 FROM DUAL UNION ALLSELECT 'F', 0, 3, 0 FROM DUAL UNION ALLSELECT 'G', 0, 3, 1 FROM DUAL UNION ALLSELECT 'H', 0, 1, 5 FROM DUAL UNION ALLSELECT 'I', 3, 5, 0 FROM DUAL)SELECTcol1,nvl(last_value(nullif(col2, 0)) IGNORE NULLS OVER (ORDER BY col1), 0) col2,nvl(last_value(nullif(col3, 0)) IGNORE NULLS OVER (ORDER BY col1), 0) col3,nvl(last_value(nullif(col4, 0)) IGNORE NULLS OVER (ORDER BY col1), 0) col4FROM t
现在，让我们分解这些窗口函数：
NULLIF（colx，0）
只要我们在数据集中有一个可接受的“空”值，这就是产生NULL值的一种简单方法。 因此，我们得到的不是NULL而是零。 将此功能应用于我们的数据，我们得到：
Col1 Col2 Col3 Col4----------------------ANULL 15BNULL 4NULLC2NULL NULLDNULL NULL NULLE35NULLFNULL 3NULLGNULL 31HNULL 15I35NULL
之所以这样做，是因为现在我们可以利用一些排名函数可以使用的有用的IGNORE NULLS子句，特别是LAST_VALUE()或LAG()。 我们现在可以写：
last_value(...) IGNORE NULLS OVER (ORDER BY col1)
在按col1对行进行排序时，我们取当前行之前的最后一个非NULL值：
如果当前行包含非NULL值，那么我们将使用该值。 如果当前行包含NULL值，那么我们将“向上”运行，直到达到非NULL值 如果我们要“向上”并且没有达到任何非NULL值，那么我们得到NULL
这导致以下结果：
Col1 Col2 Col3 Col4----------------------ANULL 15BNULL 45C245D245E355F335G331H315I355
请注意，对于大多数窗口函数，一旦指定了ORDER BY子句，则将以下frame子句用作默认值：
last_value(...) IGNORE NULLS OVER (ORDER BY col1ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW)
有很多关键字，但是一旦您掌握了很多窗口函数，它们的含义就不会那么晦涩难懂了。 我们建议阅读以下博客文章以了解有关它们的更多信息：
ROW_NUMBER（），RANK（）和DENSE_RANK（）之间的区别 不要错过带有FIRST_VALUE（），LAST_VALUE（），LEAD（）和LAG（）的超凡SQL能力
最后，由于我们不希望这些NULL值保留在结果中，因此我们只需使用NVL()（或其他数据库中的COALESCE()将其删除：
nvl(last_value(...) IGNORE NULLS OVER (...), 0)
很简单，不是吗？ 请注意，在这种特殊情况下，LAG()和LAST_VALUE()将具有相同的效果。
使用MODEL子句的解决方案
每当您在（Oracle）SQL中遇到问题时，使用窗口函数开始变得难以解决时，OracleMODEL子句可能会为其提供“简便”的解决方案。 我在“ easy”上使用了引号，因为语法有点难以记住，但是其本质确实并不那么难。
MODEL子句不过是一种Oracle专用的方言，用于在数据库中实现类似电子表格的逻辑。 我强烈建议阅读Oracle的相关白皮书，该白皮书很好地解释了该功能：
/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf
这是您可以使用MODEL解决问题的方法（并忍受）：
WITH t(col1, col2, col3, col4) AS (SELECT 'A', 0, 1, 5 FROM DUAL UNION ALLSELECT 'B', 0, 4, 0 FROM DUAL UNION ALLSELECT 'C', 2, 0, 0 FROM DUAL UNION ALLSELECT 'D', 0, 0, 0 FROM DUAL UNION ALLSELECT 'E', 3, 5, 0 FROM DUAL UNION ALLSELECT 'F', 0, 3, 0 FROM DUAL UNION ALLSELECT 'G', 0, 3, 1 FROM DUAL UNION ALLSELECT 'H', 0, 1, 5 FROM DUAL UNION ALLSELECT 'I', 3, 5, 0 FROM DUAL)SELECT * FROM tMODELDIMENSION BY (row_number() OVER (ORDER BY col1) rn)MEASURES (col1, col2, col3, col4)RULES (col2[any] = DECODE(col2[cv(rn)], 0, NVL(col2[cv(rn) - 1], 0), col2[cv(rn)]),col3[any] = DECODE(col3[cv(rn)], 0, NVL(col3[cv(rn) - 1], 0), col3[cv(rn)]),col4[any] = DECODE(col4[cv(rn)], 0, NVL(col4[cv(rn) - 1], 0), col4[cv(rn)]))
这里有三个有趣的子句：
DIMENSION BY子句
就像在Microsoft Excel电子表格中一样，DIMENSION对应于每个电子表格单元格的连续，不同的索引，我们要通过该索引来访问该单元格。 在Excel中，总是有两个维（一个维用字母A..Z，AA..ZZ，…书写），另一个维用数字（1..infinity）书写。
使用MODEL，您可以指定任意多个尺寸。 在我们的示例中，我们将只使用一个，即每行的行号，该行号由col1排序（窗口函数的另一种用例）。
MEASURES子句
MEASURES子句为每个“单元”指定单独的单元值。 在Microsoft Excel中，一个单元格只能有一个值。 在Oracle的MODEL子句中，我们可以在一个“单元格”中一次处理多个值。
在这种情况下，我们将所有列都设为单元格。
RULES子句
这是MODEL子句中真正有趣的部分。 在这里，我们指定要根据哪些规则来计算每个单个单元格的值。 语法很简单：
RULES (<rule 1>,<rule 2>,...,<rule N>)
每个单独的规则都可以实现以下形式的分配：
RULES (cell[dimension(s)] = rule)
在我们的例子中，我们将对单元格col2，col3和col4以及维度rn任何值（对于行号）重复相同的规则。 因此，作业的左侧是
RULES (col2[any] = rule,col3[any] = rule,col4[any] = rule,)
右侧是一个平凡的（但看起来不平凡的）表达：
DECODE(col2[cv(rn)], 0, NVL(col2[cv(rn) - 1], 0), col2[cv(rn)])
让我们再次分解。
解码
DECODE是一个简单实用的Oracle函数，它接受第一个参数，将其与参数2进行比较，如果它们相同，则返回参数3，否则返回参数4。它的作用类似于CASE，有点冗长：
DECODE(A, B, C, D)-- The same as:CASE A WHEN B THEN C ELSE D END
简历（rn）
cv()是特定于MODEL“函数”，表示“当前值”。 在赋值的左侧，我们使用"any"作为维度说明符，因此我们将此规则应用于rn“ any”值。 为了访问特定的rn值，我们只需编写cv(rn)或“ rn的当前值”。
递归性
允许MODEL子句的RULES跨越一棵递归树（尽管不是图，所以不允许循环），其中每个单元格都可以基于前一个单元格进行定义，而前一个单元格又是基于其前身定义的。 我们通过col2[cv(rn) - 1]，其中cv(rn) - 1表示“当前行号减一”。
容易吧？ 授予。 语法不是简单明了的，我们只是在摸索MODEL可能的表面。
结论
SQL提供了一些很酷的方法来实现数据驱动的，声明性的数据规范。MODEL子句有点怪异，但同时功能非常强大。 窗口函数要容易得多，也要快一些。窗口函数应该是每个使用SQL的开发人员的工具链中的工具。
在本文中，我们展示了如何使用窗口函数或MODEL填补稀疏数据中的空白。 一个类似的用例是运行总计。 如果本文引起了您的兴趣，建议您阅读有关SQL中计算运行总计的不同方法 。
翻译自: //12/fill-sparse-data-previous-non-empty-value-sql.html
sql填充空值
