<p>这个问题实际上有两个部分。一个对应于正确对齐表的数据。另一种是处理日期格式的混乱。在</p>
<hr/>
<p>现在假设有两个sql函数begin_time()和end_time()。我马上在下面讨论。在</p>
<p>要对齐数据,请将表与自身左联接两次:</p>
<pre><code>select t.col1, t.col2, t.col3, t.col4,
parse_begin_time(bt.col4) as timeBegin,
parse_end_time(et.col4) as timeEnd
from yourtable t
left join yourtable as bt on begin_t.col2 = t.col1 and bt.col3 = 'timeBegin'
left join yourtable as et on end_t.col2 = t.col1 and et.col3 = 'timeEnd'
where t.col3 not in ('timeBegin', 'timeEnd');
</code></pre>
<p>如果由于存在多个条目而产生多个条目,请使用聚合:</p>
^{pr2}$
<p>注意:如果您有大量的数据,那么上面的操作不会特别好。在create表中运行它们一次。。。语句并删除原始架构,或创建一个物化视图以供将来使用。在</p>
<hr/>
<p>然后,您需要考虑如何格式化混乱的timeBegin和timeEnd字段,我假设它们存储在文本字段中。会是这样的:</p>
<pre><code>create or replace function parse_begin_time(text) returns date as $$
declare
_input text := $1;
_output text;
_bc boolean := false;
_y text;
_m text;
_d text;
_tmp text;
_i int;
begin
_input := trim(both from _input);
PG is fine with '200-01-01 BC' as a date, but not with '-200-01-01'
if left(_input, 1) = '-'
then
_bc := true;
_input := right(_input, -1);
end if;
Extract year, month and day
_tmp := _input;
_i := position(_tmp for '-');
_y := substring(_tmp from 1 for i - 1);
_tmp := substring(_tmp from i);
_i := position(_tmp for '-');
_m := substring(_tmp from 1 for i - 1);
_tmp := substring(_tmp from i);
_i := position(_tmp for '-');
_d := substring(_tmp from 1 for i - 1);
if _tmp <> '' or left(trim(left '0' from _y), 1) = 'X'
then
raise exception 'invalid date input: %', _input;
end if;
Prevent locale-specific text to date conversion issues with one or two digit years
e.g. rewrite 1-2-3 as 0001-02-03.
if length(_y) < 4
then
_y := lpad(_y, 4, '0');
end if;
if length(_m) < 2
then
_m := lpad(_m, 2, '0');
end if;
if length(_d) < 2
then
_d := lpad(_m, 2, '0');
end if;
Process year, month, day
Add suitable logic here per your specs, using string and date functions
http://www.postgresql.org/docs/current/static/functions-string.html
http://www.postgresql.org/docs/current/static/functions-formatting.html
http://www.postgresql.org/docs/current/static/functions-datetime.html
for end-of-months, use the built-in arithmetics, e.g.:
_date := _date + interval '1 month' - interval '1 day'
Build _output
_output := _y || '-' || _m || '-' || _d;
if _bc
_output := _output || ' BC';
end if;
return _output::date;
end;
$$ language plpgsql strict stable;
</code></pre>
<p>如果您对它比较熟悉,可以选择使用plpython或plpythonu。我想您对这两个方面的了解要比我多,而且肯定有足够的python来编写所需的代码。劳伦斯的代码是另一个很好的起点,如果您更愿意使用plpgsql。在</p>
<p><code>strict</code>语句告诉Postgres不要在空输入上调用函数,而是立即返回null。您可能不希望它用于结束时间函数。在</p>