sql - PostgreSQL:在 GROUP BY 之后使用 LEAST() 命令实现第一笔交易

Question

我正在使用这样的 magento 表:

+-----------+--------------+------------+--------------+----------+-------------+
| date      | email        | product_id | product_type | order_id | qty_ordered |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | x@y.com      | 18W1       | custom       | 12       | 1           |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | x@y.com      | 18W2       | simple       | 17       | 3           |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/20 | z@abc.com    | 22Y34      | simple       | 119      | 1           |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/20 | z@abc.com    | 22Y35      | custom       | 31       | 2           |
+-----------+--------------+------------+--------------+----------+-------------+

我想通过按电子邮件分组来创建一个新 View ，然后只取 order_id 最少的行。

所以从上面执行此操作后我的最终表应该如下所示:

+-----------+--------------+------------+--------------+----------+-------------+
| date      | email        | product_id | product_type | order_id | qty_ordered |  
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | x@y.com      | 18W1       | custom       | 17       | 1           |
+-----------+--------------+------------+--------------+----------+-------------+
| 2017/2/15 | z@abc.com    | 18W2       | simple       |   31     | 3           |
+-----------+--------------+------------+--------------+----------+-------------+

我正在尝试使用以下查询(但它不起作用):

SELECT * , (SELECT DISTINCT table.email, table.order_id,  
    LEAST (order_id) AS first_transaction_id
FROM
    table
GROUP BY
    email)
FROM table;

真的很想得到任何帮助，谢谢!

Answer 1

我想你想要distinct on:

select distinct on (email) t.*
from t
order by email, order_id;

distinct on 是一个 Postgres 扩展。根据 order by 子句，它为括号中的所有键组合获取一条记录。在这种情况下，每个 email 一行，第一个是具有最小 order_id 的那个(因为 order by) . select 中的键也需要是 order by 中的第一个键。