sql经典代码

开窗函数

row_num()

• 注意，row_num() 和 row_num 是不同的

    drop table hsh_nullTest;
    CREATE TABLE IF NOT EXISTS hsh_nullTest
    (
        id INT
        ,first_col INT
        ,second_col STRING
    );
    INSERT INTO hsh_nullTest VALUES (0,0,null);
    INSERT INTO hsh_nullTest VALUES (1,0,'hesy');
    INSERT INTO hsh_nullTest VALUES (2,1,'test') ;
    INSERT INTO hsh_nullTest VALUES (3,1,'test hesy') ;
    INSERT INTO hsh_nullTest VALUES (4,1,'test Tokens') ;
    INSERT INTO hsh_nullTest VALUES (5,2,null);

    SELECT * FROM hsh_nullTest;

    select 
        id,
        first_col,
        row_number() over(
            partition by first_col
        ) 
    from hsh_nullTest
        where second_col is not null
    ;