sql - 解析函数FIRST_VALUE在SQL中是如何工作的

Question

我刚刚开始深入研究 SQL 分析函数，对 FIRST_VALUE 有点犹豫，也许它只是特定于来自 this 的示例查询网站:

-- How many days after the first hire of each department were the next
-- employees hired?

SELECT empno, deptno, hiredate ? FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;

 EMPNO     DEPTNO    DAY_GAP
---------- ---------- ----------
  7369         20          0
  7566         20        106
  7902         20        351
  7788         20        722
  7876         20        756
  7499         30          0
  7521         30          2
  7698         30         70
  7844         30        200
  7654         30        220
  7900         30        286

我正在努力解决这个查询的两个部分。首先，什么是“？”在这里做什么？我之前只看到它用于参数化查询。其次，DAY_GAP 实际是如何计算的？查询如何知道根据此查询计算天数差异？它与hiredate的数据类型有关吗？

Answer 1

First, what is the '?' doing here?

这是一个错误；它应该是 - (减号)。我猜测 Basu 将他的 Oracle 抄本复制并粘贴到一个程序中，该程序将 ASCII 减号静默转换为 Unicode 短划线，然后再将非 ASCII 字符静默转换为 ? .

Second, how is the DAY_GAP actually being calculated? How does the query know to calculate the difference in days based on this query? Does it have something to do with the data type of hiredate?

见前文；一旦你把它写成 hiredate - FIRST_VALUE(hiredate) OVER (...) ，突然说得通了! :-)