当first_value由于varchar值的大小而引发错误时，可供选择的first_value/解决方案

Question

SELECT 
ID,
FIRST_VALUE(User) over (partition by id order by date) as firstUser,
FIRST_VALUE(Comment) over (partition by id order by date) as FirstComment,
FIRST_VALUE(User) over (partition by id order by date desc) as lastUser,
FIRST_VALUE(Comment) over (partition by id order by date desc) as LastComment
from table;

前。

- ID | User | Date | Comment
- 1 | user1 | 2019/06/25 | comment1
- 1 | user2 | 2019/06/26 | comment2

预期结果

- ID | firstUser | FirstComment | lastUser | lastComment  
 1 | user1 | comment1 | user2 | comment2

Answer 1

我认为你不应该犯这样的错误。

但是，如果是这种情况，并且您希望采用其他方法，那么下面是为您编写的代码：

-- I have used || as a separator.. You can use your own separator, if needed
WITH DATAA (ID1,USER1,DATE1,COMMENT1) AS
(SELECT 1, 'USER1',DATE '2019-06-25','comment1' FROM DUAL UNION ALL
SELECT 1, 'USER2',DATE '2019-06-26','comment2' FROM DUAL)
SELECT
    ID1,
    REGEXP_SUBSTR(USER1, '[^(||)]+') AS FIRSTUSER,
    REGEXP_SUBSTR(COMMENT1, '[^(||)]+') AS FIRSTCOMMENT,
    REGEXP_SUBSTR(USER1, '[^(||)]+$') AS LASTUSER,
    REGEXP_SUBSTR(COMMENT1, '[^(||)]+$') AS LASTCOMMENT
FROM
    (
        SELECT
            ID1,
            RTRIM(XMLAGG(XMLELEMENT(E, USER1, '||').EXTRACT('//text()')
                ORDER BY
                    DATE1
            ).GETCLOBVAL(), '||') AS USER1,
            RTRIM(XMLAGG(XMLELEMENT(E, COMMENT1, '||').EXTRACT('//text()')
                ORDER BY
                    DATE1
            ).GETCLOBVAL(), '||') AS COMMENT1
        FROM
            DATAA
        GROUP BY
            ID1
    );

db<>小提琴演示