postgresql 为带有分组的视图生成 id 行

Generate id row for a view with grouping

提问by Random Joe

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 DISTINCTdoesn't work either. Any ideas?

我最终得到了重复的行。SELECT DISTINCT也不起作用。有任何想法吗？

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:

WP表：

create table wp(
    id serial unique,   
    wp_name text primary key,
    start_time time,
    end_time time,
    description text,
    status text    
);

View Output:

查看输出：

回答by Erwin Brandstetter

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;

Major points

要点

• generate_series()is applied afteraggregate functions, but produces multiple rows, thereby multiplying all output rows.
  The window function row_number()is also applied afteraggregate functions, but only generates a single number per row. You need PostgreSQL 8.4 or later for that.

• generate_series()在聚合函数之后应用，但产生多行，从而乘以所有输出行。
  的窗口函数row_number()也被施加后的聚集函数，但只生成一个每行单号。为此，您需要 PostgreSQL 8.4 或更高版本。

• date_trunc()is redundant in date_trunc('day', t.transaction_time)::date.
  t.transaction_time::dateachieves the same, simper & faster.

• Use count(*)instead of count(t). Same result here, but a bit faster.

• date_trunc()在 中是多余的date_trunc('day', t.transaction_time)::date。
  t.transaction_time::date实现相同，更简单和更快。

• 使用count(*)代替count(t)。这里的结果相同，但速度更快。

Some other minor changes.

其他一些小的变化。