[sys\u vw.create\u date\u 1]不是有效的group by表达式
错误来自于你使用 GROUP BY 在决赛中 SELECT 查询。它指出您正在引用/使用 Workflow_Parsed.create_date 在查询中作为非组列，但它不是 GROUP BY _ARTICLE_ID, _email 表达式，即与 [Workflow_Parsed.create_date] is not a valid group by expression 如果您稍微简化一下查询，您将收到。
snowflake不允许在窗口函数表达式上进行聚合，如果您想混合 GROUP BY 使用窗口函数，尝试将查询嵌套在结构中，例如 SELECT cols, aggregate(cols) FROM (SELECT cols, window(cols)) GROUP BY cols 将两者分开（即首先对所有行应用窗口函数，然后对它生成的整个结果进行分组）。
我不确定窗口函数在您的示例查询中尝试了什么，因为我没有看到代理的 n ± 1 关系在其中的任何位置，但是根据您描述的需求和包含的示例输出，以下应该可以工作（它只使用标量子查询，没有窗口函数）：

WITH workflows AS (
  SELECT PARSE_JSON('{"ID":"123123","workflowHistory":[{"id":"666","workflowType":"CCC","entityId":"123123","creator":{"id":"503081","displayName":"AGENT2","email":"AGENT2@SOMETHING.com","userAvatarUrl":"XXXXXXX"},"createdDate":"2020-04-30T21:58:09Z","deletor":null,"deletedDate":null,"clientId":"000000000","value":"00000000"},{"id":"555","workflowType":"AAA","entityId":"123123","creator":{"id":"503080","displayName":"AGENT1","email":"AGENT1@SOMETHING.com","userAvatarUrl":"XXXXXXX"},"createdDate":"2020-04-30T21:55:09Z","deletor":null,"deletedDate":null,"clientId":"000000000","value":"00000000"},{"id":"444","workflowType":"xyz","entityId":"123123","creator":{"id":"503080","displayName":"AGENT1","email":"AGENT1@SOMETHING.com","userAvatarUrl":"XXXXXXX"},"createdDate":"2020-04-30T21:19:09Z","deletor":null,"deletedDate":null,"clientId":"000000000","value":"00000000"},{"id":"333","workflowType":"BBB","entityId":"123123","creator":{"id":"503079","displayName":"AGENT0","email":"AGENT0@SOMETHING.com","userAvatarUrl":"XXXXXXX"},"createdDate":"2020-04-30T21:10:09Z","deletor":null,"deletedDate":null,"clientId":"000000000","value":"00000000"},{"id":"222","workflowType":"ZZZ","entityId":"123123","creator":{"id":"503079","displayName":"AGENT0","email":"AGENT0@SOMETHING.com","userAvatarUrl":"XXXXXXX"},"createdDate":"2020-04-30T21:08:09Z","deletor":null,"deletedDate":null,"clientId":"000000000","value":"00000000"}]}') AS request
), workflow_rows AS (
  SELECT
    w.request:ID::varchar AS article_id,        
    lf.value:createdDate::timestamp_tz  AS created_date,
    lf.value:creator.id::integer AS creator_id,
    lf.value:creator.email::varchar AS creator_email,
    lf.value:workflowType::varchar AS workflow_type,
    lf.value:value::varchar AS workflow_value
  FROM workflows w, LATERAL FLATTEN(REQUEST:workflowHistory) lf
), article_workflow_creators AS (
  SELECT DISTINCT
    article_id,
    creator_id,
    creator_email
  FROM workflow_rows
)
SELECT
    awc.article_id,
    awc.creator_id,
    awc.creator_email,
    (SELECT MAX(wr.created_date) FROM workflow_rows wr WHERE wr.article_id = awc.article_id AND wr.creator_id = awc.creator_id) AS last_date_value,
    (SELECT MIN(wr.created_date) FROM workflow_rows wr WHERE wr.article_id = awc.article_id AND wr.creator_id = awc.creator_id) AS first_date_value,
    (SELECT MAX(wr.created_date) FROM workflow_rows wr WHERE wr.article_id = awc.article_id AND wr.creator_id = awc.creator_id - 1) AS previous_date,
    (SELECT MAX(wr.created_date) FROM workflow_rows wr WHERE wr.article_id = awc.article_id AND wr.creator_id = awc.creator_id + 1) AS next_date
FROM article_workflow_creators awc;

对于问题中包含的单个json行输入，这将生成：

+------------+------------+----------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| ARTICLE_ID | CREATOR_ID | CREATOR_EMAIL        | LAST_DATE_VALUE               | FIRST_DATE_VALUE              | PREVIOUS_DATE                 | NEXT_DATE                     |
|------------+------------+----------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 123123     |     503081 | AGENT2@SOMETHING.com | 2020-04-30 21:58:09.000 +0000 | 2020-04-30 21:58:09.000 +0000 | 2020-04-30 21:55:09.000 +0000 | NULL                          |
| 123123     |     503080 | AGENT1@SOMETHING.com | 2020-04-30 21:55:09.000 +0000 | 2020-04-30 21:19:09.000 +0000 | 2020-04-30 21:10:09.000 +0000 | 2020-04-30 21:58:09.000 +0000 |
| 123123     |     503079 | AGENT0@SOMETHING.com | 2020-04-30 21:10:09.000 +0000 | 2020-04-30 21:08:09.000 +0000 | NULL                          | 2020-04-30 21:55:09.000 +0000 |
+------------+------------+----------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+

我分享了如何使用推荐语法的代码

WITH WorkFlow_Parsed AS(

SELECT ARTICLE_ID,
       HARVEST_DATE,
       value:createdDate::timestamp_tz  AS create_date,                
       value:creator:email AS email,
       value:workflowType AS  workflowType,
       value:value AS value

FROM 'QWERTY', lateral flatten( input => REQUEST:workflowHistory )
)

SELECT _ARTICLE_ID, _email, last_date_value,first_date_value,
MIN(lag_value),
MAX(lead_value)
FROM (
SELECT 
DISTINCT 
WorkFlow_Parsed.ARTICLE_ID AS _ARTICLE_ID,
WorkFlow_Parsed.email AS _email,
last_value(WorkFlow_Parsed.create_date) over (partition by WorkFlow_Parsed.email,WorkFlow_Parsed.ARTICLE_ID order by WorkFlow_Parsed.create_date) AS last_date_value,
first_value(WorkFlow_Parsed.create_date) over (partition by WorkFlow_Parsed.email,WorkFlow_Parsed.ARTICLE_ID order by WorkFlow_Parsed.create_date) AS first_date_value,
COALESCE(LAG(WorkFlow_Parsed.create_date) IGNORE NULLS over (partition by  WorkFlow_Parsed.ARTICLE_ID order by WorkFlow_Parsed.create_date),'1900-01-01 00:00:00') AS lag_value,
COALESCE(LEAD(WorkFlow_Parsed.create_date) IGNORE NULLS over (partition by WorkFlow_Parsed.ARTICLE_ID order by WorkFlow_Parsed.create_date),'2100-01-01 00:00:00') AS lead_value
FROM  WorkFlow_Parsed) GROUP BY _ARTICLE_ID,_email,last_date_value,first_date_value