第1季：FIRST_VALUE / LAST_VALUE
问题2：PARTITION BY（正如罗曼·佩卡（Roman Pekar）所建议的那样）
在这里查看
SELECt DISTINCT i.id AS id, i.userid AS userid, i.itemname AS itemname, COALESCE(LEAD(i.id) OVER (ORDER BY i.created DESC) ,FIRST_VALUE(i.id) OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextitemid, COALESCE(LAG(i.id) OVER (ORDER BY i.created DESC) ,LAST_VALUE(i.id) OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS previtemid, COALESCE(LEAD(i.id) OVER (PARTITION BY i.userid ORDER BY i.created DESC) ,FIRST_VALUE(i.id) OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextuseritemid, COALESCE(LAG(i.id) OVER (PARTITION BY i.userid ORDER BY i.created DESC) ,LAST_VALUE(i.id) OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS prevuseritemid, i.created AS createdFROM items i LEFT JOIN users u ON i.userid = u.idORDER BY i.created DESC;
