Postgresql创建视图

Question

我在表A中有以下列，其中记录了用户每次签入或从大楼结账时的指纹“交易”。

CREATE TABLE user_transactions(
    id serial PRIMARY KEY,
    staff_id INT4,
    transaction_time TIMESTAMP,
    transaction_type INT4
);

在一天内，一个用户可以有许多事务。如何创建具有以下结构的视图？

    staff_id INT4
    transaction_date DATE
    first_transaction TIMESTAMP --first finger scan of the day
    last_transaction TIMESTAMP  --last finger scan of the day
    number_of_transaction INT4  --how many times did the user scan for the day

Answer 1

这个应该可以完成这项工作：

create or replace view xxx as 
select 
    staff_id,
    date_trunc('day', transaction_time) transaction_date, 
    min(transaction_time) first_transaction, 
    max(transaction_time) last_transaction, 
    count(*) 
from user_transactions 
group by staff_id, date_trunc('day', transaction_time);