如何在Zoho Analytics中获取每位医生的第二高预约日期

问题背景 #

我有一张存储所有预约信息的表，表结构及数据如下：

需要获取每位医生的第二高预约日期，期望结果如下：

尝试过以下SQL，但无法实现需求——它仅排除了整个表的最高日期，而非每位医生自身的最高日期：

SELECT id_doc, MAX( app_date )
  FROM table1
GROUP BY id_doc
 WHERE col < ( SELECT MAX( app_date )
                 FROM table1 )

解决方法 #

Zoho Analytics支持标准SQL的窗口函数，以下两种方法均可实现需求：

方法1：使用ROW_NUMBER()窗口函数（推荐） #

SELECT id_appoint, id_doc, app_date
FROM (
    SELECT 
        id_appoint, 
        id_doc, 
        app_date,
        ROW_NUMBER() OVER(
            PARTITION BY id_doc 
            ORDER BY STR_TO_DATE(app_date, '%d/%m/%y') DESC
        ) AS row_rank
    FROM table1
) ranked_data
WHERE row_rank = 2

• PARTITION BY id_doc：按医生ID分组，确保每个医生的记录单独排序
• STR_TO_DATE(app_date, '%d/%m/%y')：将字符串格式的日期转换为日期类型，保证排序逻辑准确
• 外层筛选row_rank=2，直接获取每组的第二条记录（即第二高预约日期）

方法2：使用关联子查询 #

如果窗口函数受限，可采用子查询统计日期排名：

SELECT t1.id_appoint, t1.id_doc, t1.app_date
FROM table1 t1
WHERE (
    SELECT COUNT(DISTINCT STR_TO_DATE(t2.app_date, '%d/%m/%y'))
    FROM table1 t2
    WHERE t2.id_doc = t1.id_doc 
      AND STR_TO_DATE(t2.app_date, '%d/%m/%y') > STR_TO_DATE(t1.app_date, '%d/%m/%y')
) = 1

• 子查询统计当前医生ID下，比当前记录日期晚的不同日期数量为1时，当前记录即为该医生的第二高预约日期
• 同样用STR_TO_DATE转换日期，避免字符串比较的错误

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