I'm trying to create a view with row numbers like so:
create or replace view daily_transactions as
select
generate_series(1, count(t)) as id,
t.ic,
t.bio_id,
t.wp,
date_trunc('day', t.transaction_time)::date transaction_date,
min(t.transaction_time)::time time_in,
w.start_time wp_start,
w.start_time - min(t.transaction_time)::time in_diff,
max(t.transaction_time)::time time_out,
w.end_time wp_end,
max(t.transaction_time)::time - w.end_time out_diff,
count(t) total_transactions,
calc_att_status(date_trunc('day', t.transaction_time)::date,
min(t.transaction_time)::time,
max(t.transaction_time)::time,
w.start_time, w.end_time ) status
from transactions t
left join wp w on (t.wp = w.wp_name)
group by ic, bio_id, t.wp, date_trunc('day', transaction_time),
w.start_time, w.end_time;
I ended up with duplicate rows. SELECT DISTINCT
doesn't work either. Any ideas?
Transaction Table:
create table transactions(
id serial primary key,
ic text references users(ic),
wp text references wp(wp_name),
serial_no integer,
bio_id integer,
node integer,
finger integer,
transaction_time timestamp,
transaction_type text,
transaction_status text
);
WP table:
create table wp(
id serial unique,
wp_name text primary key,
start_time time,
end_time time,
description text,
status text
);
View Output:
CREATE OR REPLACE VIEW daily_transactions as
SELECT row_number() OVER () AS id
, t.ic
, t.bio_id
, t.wp
, t.transaction_time::date AS transaction_date
, min(t.transaction_time)::time AS time_in
, w.start_time AS wp_start
, w.start_time - min(t.transaction_time)::time AS in_diff
, max(t.transaction_time)::time AS time_out
, w.end_time AS wp_end
, max(t.transaction_time)::time - w.end_time AS out_diff
, count(*) AS total_transactions
, calc_att_status(t.transaction_time::date, min(t.transaction_time)::time
, max(t.transaction_time)::time
, w.start_time, w.end_time) AS status
FROM transactions t
LEFT JOIN wp w ON t.wp = w.wp_name
GROUP BY t.ic, t.bio_id, t.wp, t.transaction_time::date
, w.start_time, w.end_time;
generate_series()
is applied after aggregate functions, but produces multiple rows, thereby multiplying all output rows.row_number()
is also applied after aggregate functions, but only generates a single number per row. You need PostgreSQL 8.4 or later for that.date_trunc()
is redundant in date_trunc('day', t.transaction_time)::date
.t.transaction_time::date
achieves the same, simper & faster.
Use count(*)
instead of count(t)
. Same result here, but a bit faster.
Some other minor changes.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With