Create a view in PostgreSQL

Question

I have the following columns in Table A which records users fingerprint "transaction" every time they check in or check out from a building.

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

In a single day a user can have many transactions. How can I create a view that with the following stucture?

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

Phenacite

Answer 1

This one should do the job:

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);

Undecided

Thank you @A.H its working well with a little modification on date_trunc('date'..) to date_trunc('day'...) – Phenacite

Answer 2

For example, you create person table as shown below.

CREATE TABLE person (
  id INTEGER,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  age INTEGER
);

Then, you insert 2 rows into person table as shown below:

INSERT INTO person (id, first_name, last_name, age) 
VALUES (1, 'John', 'Smith', 27), (2, 'David', 'Miller', 32);

Now, you can create my_view view with SELECT statement as shown below:

CREATE VIEW my_view AS
  SELECT first_name, last_name FROM person;

Then, you can call my_view with FROM clause of SELECT statement as shown below:

postgres=# SELECT * FROM my_view;
 first_name | last_name
------------+-----------
 John       | Smith
 David      | Miller
(2 rows)

Benito