“分组依据”适用于 MySQL，但不适用于 Oracle

Question

我有一个适用于 MySQL 但不适用于 Oracle 的查询，我正在尝试进行转换。这是我的表：

unique_row_id  http_session_id  page_name   page_hit_timestamp
----------------------------------------------------------------
0              123456789        index.html  2010-01-20 15:00:00
1              123456789        info.html   2010-01-20 15:00:05
2              123456789        faq.html    2010-01-20 15:00:15
3              987654321        index.html  2010-01-20 16:00:00
4              987654321        faq.html    2010-01-20 16:00:05
5              987654321        info.html   2010-01-20 16:00:15
6              111111111        index.html  2010-01-20 16:01:00
7              111111111        faq.html    2010-01-20 16:01:05
8              111111111        info.html   2010-01-20 16:01:15

SQL 在

select http_session_id, unique_row_id, page_name, page_hit_timestamp 
from page_hits 
group by http_session_id;

MySQL 上，这将返回 3 行（每个唯一的 http_session_id 一行）。

在 Oracle 上，我收到“ORA-00979：不是 GROUP BY 表达式”错误。我也尝试过使用不同的方法，但我无法让它发挥作用。

需要明确的是 - 我想要一个 ResultSet，其中每个唯一的 http_session_id 包含一行。最好是 unique_row_id 是最大的（例如，http_session_id==123456789 为 2），但这并不重要。

我即将将其分解为多个单独的sql语句（一个“选择不同的http_session_id”，另一个迭代所有这些并选择max（unique_row_id）。任何指针将不胜感激地收到 - 我很想避免这个，

凯文。

Answer 1

您遇到ORA错误的原因是因为MySQL支持非标准GROUP BY子句，称其为“功能”。 记录在此处。

标准 SQL GROUP BY 子句必须包含 SELECT 子句中指定的 ALL 列，这些列未包装在聚合函数（LIKE COUNT、MAX/MIN 等）中，以便在 GROUP BY 子句中指定。

如果您想要每个 http_session_id 值有一个唯一的行 - 请查看使用 ROW_NUMBER：

SELECT x.*
  FROM (select http_session_id, unique_row_id, page_name, page_hit_timestamp,
               ROW_NUMBER() OVER (PARTITION BY http_session_id 
                                      ORDER BY http_session_id) AS rank
          FROM page_hits) x
 WHERE x.rank = 1

Answer 2

这行得通吗：

select max(unique_row_id), http_session_id
from page_hits
group by http_session_id

顺便说一下；对于包含在结果集中但不包含在 group by 子句中的列，我的 sql 在结果集中返回什么？ （页面名称、页面点击时间戳）

Answer 3

我认为 GROUP BY 需要在 SQL 标准中的 WHERE 子句或聚合函数中使用变量？

尝试使用SELECT MAX(unique_row_id) GROUP BY http_session_id。

Answer 4

在标准 SQL 中，如果有 GROUP BY 子句，则不属于该子句的所有列都必须聚合。在 MySQL 中，这条规则在设计上被放宽了。

例如，这在 MySQL 中是允许的，但在标准 SQL 中不允许：

SELECT customer_id, country, SUM(amount) FROM records GROUP BY customer_id

有一个警告：MySQL 假设您知道自己在做什么。如果同一客户在多个国家/地区有记录，则查询将仅获取表中的第一个国家/地区，而忽略所有其他国家/地区。此外，由于行的顺序未定义，并且没有 ORDER BY，因此每次运行查询时可能会得到不同的结果。

在标准 SQL 中，您有两种选择：

SELECT customer_id, country, SUM(amount) FROM records GROUP BY customer_id, country

或

SELECT customer_id, MIN(country), SUM(amount) FROM records GROUP BY customer_id

Answer 5

Oracle 中的另一个选项（如果需要）：

select DISTINCT
       FIRST_VALUE(unique_row_id)
       OVER (PARTITION BY http_session_id
             ORDER BY unique_row_id DESC) unique_row_id,
       http_session_id,
       FIRST_VALUE(page_name)
       OVER (PARTITION BY http_session_id
             ORDER BY unique_row_id DESC) page_name,
       FIRST_VALUE(page_hit_timestamp)
       OVER (PARTITION BY http_session_id
             ORDER BY unique_row_id DESC) page_hit_timestamp
from page_hits;

这将获得一组不同的 http_session_id，并且对于每个，返回 unique_row_id、page_name和 page_hit_timestamp 来自该 http_session_id 具有最大 unique_row_id 的行，例如：

unique_row_id  http_session_id  page_name   page_hit_timestamp
----------------------------------------------------------------
2              123456789        faq.html    2010-01-20 15:00:15
5              987654321        info.html   2010-01-20 16:00:15
8              111111111        info.html   2010-01-20 16:01:15