Postgres有条件的选择?
我真的不知道该怎么形容这个问题没有一个例子,所以这里有一个例子:Postgres有条件的选择?
我有三个表:
表1:盒
BoxID | Box Name | Box Cost
1 | Bob's Box | $20
2 | Matt's Box | $21
3 | Jacob's Box | $22
4 | Beth's Box | $23
5 | Rachel's Box | $24
表2:Box_ProcessIDs
BoxID | ParentProcessID
1 | 123
2 | 456
3 | 789
4 | 012
5 | 234
表3:Box_Processes
ParentProcess | ChildProcess | Start Time | End Time | ProcessName
123 | AAA | 1:00 | 1:05 | Invoiced
123 | AAB | 1:30 | 1:35 | Packed
123 | BBB | 2:00 | 2:05 | Shipped
456 | CDD | 3:15 | 3:20 | Invoiced
456 | DDD | 3:25 | 3:30 | Packaging_Complete
456 | CCD | 3:35 | 3:40 | Shipped
789 | EEE | 4:15 | 4:20 | Invoiced
789 | EEF | 4:25 | 4:30 | Done_Packing
789 | EFF | 4:35 | 4:40 | Shipped
我希望输出列是: 箱名称,箱成本,箱装运期限,装盒时间,盒发票时间
我知道我可以使用类似%包%选择我的包装领域,我可以计算出如何使用Date_Part或某些来获得我的持续时间,但我不知道如何将shipping duration
,packing duration
和invoice duration
分成不同的字段。
所以你想要做这样的伪sql?
select b.box_name, b.box_cost,
bp2.end_time - bp2.start_time as box_shipping_duration,
bp3.end_time - bp3.start_time as box_packing_duration,
bp1.end_time - bp1.start_time as box_invoice_duration
from boxes b
join box_processids bpid on b.boxid = bpid.boxid
left outer join box_processes bp1 on bpid.parentprocessid = bp1.parentprocess
and processname = 'Invoiced'
left outer join box_processes bp2 on bpid.parentprocessid = bp2.parentprocess
and processname = 'Shipped'
left outer join box_processes bp3 on bpid.parentprocessid = bp3.parentprocess
and processname = 'Packed'
(需要null检查,妥善处理日期时间,更好的名称等)
谢谢 - 这正是我一直在寻找的!是否有这种问题/方法/解决方案的名称? – user2057932 2013-02-10 02:37:37
首先,你的设计是有缺陷的。 time
对此没有好处。你一过午夜就会中断。改为使用timestamp
or timestamptz
。
接下来,由于在PostgreSQL中未加引号的标识符会自动转换为小写,所以您的命名约定也不好。我将它改编成更有用的东西。然后
CREATE TABLE boxes (box_id int PRIMARY KEY, box_name text, box_cost numeric);
INSERT INTO boxes VALUES
(1, 'Bob''s Box', 20)
,(2, 'Matt''s Box', 21)
,(3, 'Jacob''s Box', 22)
,(4, 'Beth''s Box', 23)
,(5, 'Rachel''s Box', 24);
CREATE TABLE box_processids (
box_id int
,parentprocess_id int
,PRIMARY KEY(box_id, parentprocess_id)
);
INSERT INTO box_processids VALUES
(1, 123)
,(2, 456)
,(3, 789)
,(4, 012)
,(5, 234);
CREATE TABLE box_processes (
parentprocess_id int
,childprocess_id char(3)
,start_time timestamp
,end_time timestamp
,processname text
,PRIMARY KEY(parentprocess_id, childprocess_id)
);
INSERT INTO box_processes VALUES
(123, 'AAA', '2013-2-10 1:00', '2013-2-10 1:05', 'Invoiced')
,(123, 'AAB', '2013-2-10 1:30', '2013-2-10 1:35', 'Packed')
,(123, 'BBB', '2013-2-10 2:00', '2013-2-10 2:05', 'Shipped')
,(456, 'CDD', '2013-2-10 3:15', '2013-2-10 3:20', 'Invoiced')
,(456, 'DDD', '2013-2-10 3:25', '2013-2-10 3:30', 'Packaging_Complete')
,(456, 'CCD', '2013-2-10 3:35', '2013-2-10 3:40', 'Shipped')
,(789, 'EEE', '2013-2-10 4:15', '2013-2-10 4:20', 'Invoiced')
,(789, 'EEF', '2013-2-10 4:25', '2013-2-10 4:30', 'Done_Packing')
,(789, 'EFF', '2013-2-10 4:35', '2013-2-10 4:40', 'Shipped');
您的查询看起来是这样的:
SELECT b.box_name
,b.box_cost
,(i.end_time - i.start_time) AS box_invoice_duration
,(p.end_time - p.start_time) AS box_packing_duration
,(s.end_time - s.start_time) AS box_shipping_duration
FROM boxes b
LEFT JOIN box_processids bp USING (box_id)
LEFT JOIN box_processes i ON i.parentprocess_id = bp.parentprocess_id
AND i.processname = 'Invoiced'
LEFT JOIN box_processes p ON i.parentprocess_id = bp.parentprocess_id
AND p.processname = 'Packed'
LEFT JOIN box_processes s ON i.parentprocess_id = bp.parentprocess_id
AND s.processname = 'Shipped'
->sqlfiddle
使用to_char()
格式化您interval
你想要的任何方式。
在夏令时结束时,当时钟向后移动时'timestamp'会中断,所以我强烈建议'timestamptz'。基本上,当你想捕获一个时间点而不是像本地(单一时区)约会时间时,这是正确的类型。如果*在预约日期到达之前更改了DST规则,那么您希望约会在当地时间保持不变,但移至新的恒星时间,这是时间戳的行为。 – kgrittn 2013-02-10 15:23:10
As * always *,请提供PostgreSQL的版本号。 – 2013-02-10 01:26:56