在Amazon Redshift中首先实现NULLS [英] Implementing NULLS FIRST in Amazon Redshift

问题描述

我对行号使用求和窗口函数，类似于此查询-

I am using sum window function for row number, similar to this query -

SELECT field_a,
       SUM(1) OVER (PARTITION BY field_b ORDER BY field_c ASC ROWS UNBOUNDED PRECEDING) AS row_number
FROM test_table
    ORDER BY row_number;

问题在于，如果field_c为空值，它将显示在末尾。我在一开始就想要它，因此将null值视为小于所有其他值。在Oracle中，可以通过提供NULLS FIRST参数来完成此操作，但是Redshift不支持该参数。那么如何在Redshift中实现它呢？

The problem is that if field_c is a null value, it appears at the end. I want it at the beginning, so null value is treated as smaller than all other values. In Oracle, this could be done by providing NULLS FIRST argument, but its not supported in Redshift. So how do I implement it in Redshift ?

推荐答案

我希望它在开始时为[null]，因此将null值视为小于所有其他值。

I want it [null] at the beginning, so null value is treated as smaller than all other values.

使用表达式

field_c IS NOT NULL

作为第一个 ORDER BY 项。如果为NULL

TRUE
FALSE .. c> ..如果NOT NULL。

as first ORDER BY item. It evaluates to ...
FALSE .. if NULL
TRUE .. if NOT NULL.

并且 FALSE （0）排在 TRUE之前（1）。适用于任何数据类型和任何可能的值分配。

And FALSE (0) sorts before TRUE (1). Works for any data type and any possible distribution of values.

SELECT field_a,
       row_number() OVER (PARTITION BY field_b
                          ORDER BY field_c IS NOT NULL, field_c) AS row_number
FROM   test_table
ORDER  BY row_number;

这篇关于在Amazon Redshift中首先实现NULLS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！