SQL QUALIFY ROW_NUMBER 在 teradata

QUALIFY ROW_NUMBER in teradata

提问by navku

below is the oracle SQL and i want to change it in Teradata format.

下面是 oracle SQL，我想以 Teradata 格式更改它。

SELECT branch_code,
    branch_no,
    c_no,
    cd_type
FROM (
    SELECT branch_code,
        branch_no,
        c_no,
        cd_type,
        * * RANK() OVER (
            PARTITION BY c_no ORDER BY cd_type
            ) RANK * *
    FROM (
        SELECT branch_code,
            branch_no,
            c_no,
            MIN(cd_type) cd_type
        FROM EMPLOYEE
        WHERE S_CODE = 'C'
            AND (branch_no) NOT IN (
                SELECT branch_code
                FROM DEPARTMENT
                WHERE branch_code = 'ABC'
                )
        )
    )
WHERE RANK = 1

I have used QUALIFY for RANK as below .

我已将 QUALIFY 用于 RANK，如下所示。

SELECT branch_code,
    branch_no,
    c_no,
    cd_type
FROM (
    SELECT branch_code,
        branch_no,
        c_no,
        cd_type,
        * * QUALIFY ROW_NUMBER() OVER (
            PARTITION BY c_no ORDER BY cd_type
            ) * * RANK
    FROM (
        SELECT branch_code,
            branch_no,
            c_no,
            MIN(cd_type) cd_type
        FROM EMPLOYEE
        WHERE S_CODE = 'C'
            AND (branch_no) NOT IN (
                SELECT branch_code
                FROM DEPARTMENT
                WHERE branch_code = 'ABC'
                )
        )
    )
WHERE RANK = 1

But getting error that "Expected something between , and QUALIFY.

但是收到错误“期望介于 , 和 QUALIFY 之间。

Can we put QUALIFY in select statement ?

我们可以将 QUALIFY 放在 select 语句中吗？

采纳答案by dnoeth

You probably simplified your existing query as this is no valid Oracle SQL (there's no GROUP BY in the Inline View). Plus you compare branch_noto branch_codein the NOT IN.

您可能简化了现有查询，因为这不是有效的 Oracle SQL（内联视图中没有 GROUP BY）。另外，您比较branch_no于branch_code在不在家。

Otherwise Ed Gibbs' answer can be further simplified to:

否则 Ed Gibbs 的回答可以进一步简化为：

SELECT branch_code,
    branch_no,
    c_no,
    MIN(cd_type) cd_type
FROM EMPLOYEE
WHERE S_CODE = 'C'
    AND (branch_no) NOT IN (
        SELECT branch_no
        FROM DEPARTMENT
        WHERE branch_code = 'ABC'
        )
GROUP BY branch_code,
    branch_no,
    c_no
QUALIFY
   RANK()
   OVER (PARTITION BY c_no 
         ORDER BY MIN(cd_type) = 1

If branch_nois defined as NULLable you should also rewrite NOT INto NOT EXISTS

如果branch_no被定义为可为空，你也应该重写NOT IN到NOT EXISTS

回答by Ed Gibbs

Normally, analytic values like RANKare calculated second to last, after joining and filtering and GROUP BYand HAVING. The only thing done after analytic values is ORDER BY. That's why in Oracle you need to put the RANKinto an inner query and then test its value in an outer query. In Teradata, QUALIFYis executed after the analytic functions and before the ORDER BY, meaning you don't need the outer query to test the RANKvalue.

通常情况下，解析值喜欢RANK被倒数第二个计算的，加入和滤波和后GROUP BY和HAVING。解析值之后唯一要做的事情是ORDER BY。这就是为什么在 Oracle 中您需要将 放入RANK内部查询，然后在外部查询中测试其值。在 Teradata 中，QUALIFY在分析函数之后和 之前执行ORDER BY，这意味着您不需要外部查询来测试RANK值。

I don't have access to Teradata today, so this query isn't tested but I like to think it's close:

我今天无法访问 Teradata，所以这个查询没有经过测试，但我认为它很接近：

SELECT branch_code,
    branch_no,
    c_no,
    cd_type
FROM (
    SELECT branch_code,
        branch_no,
        c_no,
        MIN(cd_type) cd_type
    FROM EMPLOYEE
    WHERE S_CODE = 'C'
        AND (branch_no) NOT IN (
            SELECT branch_code
            FROM DEPARTMENT
            WHERE branch_code = 'ABC'
        )
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY c_no ORDER BY cd_type) = 1