Postgres join and distinct query

Question

我有两个表盘

user
id |  name   
-------------
1  |  User1 | 
2  |  User2 |
3  |  User3 |
4  |  User4 |

用户可以随时更改名称。 还有另一个表盘

order
id |user_name | user_id | price | order_date  
---------------------------------------------
1  |  OldUser3| 3       |  5    | 2017-07-12 08:01:00.000000
2  |  NewUser3| 3       |  6    | 2017-07-12 09:01:00.000000
3  |  User1   | 1       |  8    | 2017-07-12 10:01:00.000000
4  |  NewUser |         |  10   | 2017-07-12 11:01:00.000000
5  |  NewUser |         |  100  | 2017-07-12 12:01:00.000000

user_name从表用户复制的，如果用户多次更改名称，则可能会有不同的值。

如果不是注册用户，则user_id可以为空

我需要这样的结果表

order
no |user_name | user_id | total_pr| count |  last_order
---------------------------------------------
1  |  NewUser3| 3       |  11     |   2   |2017-07-12 09:01:00.000000
2  |  User1   | 1       |  8      |   1   |2017-07-12 10:01:00.000000
3  |  NewUser |         |  10     |   1   |2017-07-12 11:01:00.000000
4  |  NewUser |         |  100    |   1   |2017-07-12 12:01:00.000000

user_name值必须取自最大order_date，并且需要按任何列排序 如果user_id为空，则所有具有相同名称的用户都是不同的用户

我试试这个

SELECT  order.user_id, order.user_name, SUM(price), COUNT(order.user_id), MAX(order_date) 
FROM order, user
WHERE  
order.order_date  >= '2017-07-01 08:01:00.000000'
AND order.order_date  <= '2017-07-15 08:01:00.000000'
GROUP BY user_id, user_name ORDER BY count ASC

但并非全部

Answer 1

试试这个

with users_cte (user_name,user_id,total_pr,count,last_order) as (
--Fetching data for members who are in users table
Select user_name,user_id,total_pr,count,last_order from (
SELECT  o.user_name, o.user_id, row_number() over (partition by o.user_id order by order_date desc) rno
, SUM(price) over (partition by o.user_id) as total_pr, COUNT(o.user_id) over(partition by o.user_id) as count , MAX(order_date)  over (partition by o.user_id) as last_order
FROM orders o
left join users u
on o.user_id = u.id
WHERE
u.id is not null
and o.order_date  >= '2017-07-01 08:01:00.000000'
AND o.order_date  <= '2017-07-15 08:01:00.000000'
) A WHere A.rno=1
union all
--Fetching data for new members   
SELECT o.user_name,null as user_id
, SUM(price) as total_pr, COUNT(o.user_name), MAX(order_date) 
FROM orders o
left join users u
on o.user_id = u.id
WHERE
u.id is null
and o.order_date  >= '2017-07-01 08:01:00.000000'
AND o.order_date  <= '2017-07-15 08:01:00.000000'
GROUP BY o.user_name
)
Select row_number() over(order by last_order) as no,* from users_cte

Answer 2

尝试：

SELECT  order.id, order.user_name, SUM(price), COUNT(order.user_id), MAX(order_date) 
FROM order 
LEFT OUTER JOIN user on order.user_id = user.id
WHERE  
order.order_date  >= '2017-07-01 08:01:00.000000'
AND order.order_date  <= '2017-07-15 08:01:00.000000'
GROUP BY user_id, user_name ORDER BY count ASC