我正在尝试创建一个包含行号的视图,如下所示：
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,transaction_time),w.end_time;
我最终得到了重复的行. SELECT DISTINCT也不起作用.有任何想法吗？
交易表：
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表：
create table wp(
id serial unique,wp_name text primary key,start_time time,end_time time,description text,status text
);
查看输出：
解决方法
CREATE OR REPLACE VIEW daily_transactions as SELECT row_number() OVER () AS id,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,w.end_time) AS status FROM transactions t LEFT JOIN wp w ON t.wp = w.wp_name GROUP BY t.ic,t.transaction_time::date,w.end_time;
主要观点
> generate_series()在聚合函数之后应用,但会生成多行,从而将所有输出行相乘.
window function row_number()也在聚合函数之后应用,但每行只生成一个数字.你需要Postgresql 8.4或更高版本.
> date_trunc()在date_trunc(‘day’,t.transaction_time):: date中是多余的.
t.transaction_time :: date实现相同,简单和简单快点.
>使用count(*)代替count(t).这里的结果相同,但速度要快一些.
其他一些细微的变化.
