varianza una son que nativas las función funciones funcion consulta agregados agregada agregación agregacion sql postgresql view window-functions sql-view

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 ventana row_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 en date_trunc(''day'', t.transaction_time)::date .
    t.transaction_time::date logra lo mismo, simper y más rápido.

  • Use count(*) lugar de count(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: