sql - son - Generar una fila de id para una vista con agrupación
que son las funciones de agregacion en sql (1)
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;
Puntos principales
-
generate_series()
se aplica después de las funciones de agregado, pero produce múltiples filas , lo que multiplica todas las filas de salida.
La función de ventanarow_number()
también se aplica después de las funciones de agregado, pero solo genera un número único por fila . Necesita PostgreSQL 8.4 o posterior para eso.
date_trunc()
es redundante endate_trunc(''day'', t.transaction_time)::date
.
t.transaction_time::date
logra lo mismo, simper y más rápido.Use
count(*)
lugar decount(t)
. El mismo resultado aquí, pero un poco más rápido.
Algunos otros cambios menores.
Intento crear una vista con números de fila así:
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;
Terminé con filas duplicadas. SELECT DISTINCT
tampoco funciona. ¿Algunas ideas?
Tabla de transacciones:
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
);
Tabla de WP:
create table wp(
id serial unique,
wp_name text primary key,
start_time time,
end_time time,
description text,
status text
);
Ver salida: