如何在含日期列的表上定义视图，新增IsNew列标识元组首次出现

以下两种SQL写法都能实现你需要的视图，因为你不关注性能，任选其一测试业务逻辑即可：

方法一：使用窗口函数（简洁直观） #

CREATE VIEW YourViewName AS
SELECT
    EntryDate,
    Dim1,
    Dim2,
    Dim3,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY Dim1, Dim2, Dim3 ORDER BY EntryDate) = 1 THEN 'true'
        ELSE 'false'
    END AS IsNew
FROM YourTableName;

逻辑说明：按Dim1、Dim2、Dim3的组合分组，每组内按EntryDate升序排序，每组中最早出现的记录（行号为1）标记IsNew为true，后续出现的标记为false。

方法二：使用NOT EXISTS子查询 #

CREATE VIEW YourViewName AS
SELECT
    t1.EntryDate,
    t1.Dim1,
    t1.Dim2,
    t1.Dim3,
    CASE
        WHEN NOT EXISTS (
            SELECT 1
            FROM YourTableName t2
            WHERE t2.Dim1 = t1.Dim1
              AND t2.Dim2 = t1.Dim2
              AND t2.Dim3 = t1.Dim3
              AND t2.EntryDate < t1.EntryDate
        ) THEN 'true'
        ELSE 'false'
    END AS IsNew
FROM YourTableName t1;

逻辑说明：对每条记录，检查是否存在相同维度组合且EntryDate更早的记录，若不存在则标记IsNew为true，否则为false。

内容的提问来源于stack exchange，提问作者Chris Harrington