在SUM子句中使用LAG或Other函数

在SUM子句中使用LAG或Other函数

更新：以 这种方式尝试

SELECt full_name,        COUNT(DISTINCT entityid) ticket_count,       COUNT(DISTINCT series) - COUNT(DISTINCT entityid) touch_count  FROM(  SELECt plannerid, full_name, entityid,          rnum - ROW_NUMBER() OVER (PARTITION BY plannerid, entityid ORDER BY rnum) series    FROM  (      SELECt p.plannerid, UPPER(p.plannersurname || ', ' || p.plannerfirstname) full_name,h.historydate, h.entityid,ROW_NUMBER() OVER (PARTITION BY p.plannerid ORDER BY h.historydate) rnum      FROM ipcs_audit_history h JOIN ipcs_planner p        ON h.plannerid = p.plannerid     WHERe h.historydate >= TO_DATE('30-DEC-13')       AND h.historydate <  TO_DATE('30-DEC-13') + 1  ) q) p GROUP BY plannerid, full_name

样本输出：

| FULL_NAME | TICKET_COUNT | TOUCH_COUNT || ----------- | -------------- || ------------- || 美国能源部，约翰| 4 | 1 |

这是 SQLFiddle 演示