Hi,
datetest=# select version(); version
----------------------------------------------------------------------PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by
GCCgcc (GCC) 3.2.2
(1 запись)
datetest=# \d daten Таблица "public.daten"Колонка | Тип |
Модификаторы
----------------+-----------------------------+-------------------------------------------------------id |
integer | not null default
nextval('public.daten_id_seq'::text)menge | integer | von | timestamp without
timezone | bis | timestamp without time zone |
Индексы: daten_pkey ключевое поле btree (id)
datetest=# select * from daten;id | menge | von | bis
----+-------+---------------------+--------------------- 1 | 2 | 2000-01-01 10:00:00 | 2000-01-01 12:00:00 2 |
3| 2000-01-01 11:00:00 | 2000-01-01 14:00:00 3 | 1 | 2000-01-01 14:00:00 | 2000-01-01 15:00:00 4 | 9 |
2000-01-0112:00:00 | 2000-01-01 16:00:00 5 | 4 | 2000-01-01 10:00:00 | 2000-01-01 11:00:00
(записей: 5)
datetest=# select * from (select (select count(1) from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as bar join (select (select count(1)+1 from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as foo using (number);
ERROR: ExecEvalExpr: unknown expression type 108
To explain what I'm actually trying to do, I'll at first visualize the
data:
10 11 12 13 14 15 16 17 t
--------------------------------------------------->
[-----2------] [---------3--------] [---1--] [----------9-------------]
[---4--]
This displays use periods for microphones, with the number of
microphones for each period of time. So from 10 to 12, one guy needs 2
microphones, and from 11 to 14 another guy needs 3, and so on.
Now let's suppose a third guy asks, if we have enough microphones for
his event from 10 to 17. I know how many microphones there are at all.
So I want to know the maximum number of microphones that are used at a
point in time within that period 10-17. That number is 12 obviously.
It's easy to find out how many microphones are in use at a certain point
in time:
datetest=# select sum(menge) from daten where von < '2000-01-01 10:30'
and bis > '2000-01-01 10:30';sum
----- 6
(1 запись)
So I could ask the maximum of sum for each point in time from 10 to 17.
This is a lot of points in time. So if two points in time don't differ
with respect to the number of microphones in use, I want to look at only
one of them. For that I query the points in time where something
changes:
datetest=# select von from daten union select bis as von from daten; von
---------------------2000-01-01 10:00:002000-01-01 11:00:002000-01-01 12:00:002000-01-01 14:00:002000-01-01
15:00:002000-01-0116:00:00
(записей: 6)
Now I want the point in time in the middle between each two subsequent
points in time, i.e. 10:30, 11:30, 12:30, 14:30, 15:30. These would be
the points in time I have to query the sum of microphones in use, then I
have to take the maximum of these sums. To achieve that, I insert a
record counter, topdog from IRC kindly showed me how to do that:
datetest=# select (select count(1) from (select von from daten union
select bis as von from daten) as d1 where d1.von < d2.von) as number,
von from (select von from daten union select bis as von from daten) d2;number | von
--------+--------------------- 0 | 2000-01-01 10:00:00 1 | 2000-01-01 11:00:00 2 | 2000-01-01 12:00:00
3| 2000-01-01 14:00:00 4 | 2000-01-01 15:00:00 5 | 2000-01-01 16:00:00
(записей: 6)
And another one:
datetest=# select (select count(1) + 1 from (select von from daten union
select bis as von from daten) as d1 where d1.von < d2.von) as number,
von from (select von from daten union select bis as von from daten) d2;number | von
--------+--------------------- 1 | 2000-01-01 10:00:00 2 | 2000-01-01 11:00:00 3 | 2000-01-01 12:00:00
4| 2000-01-01 14:00:00 5 | 2000-01-01 15:00:00 6 | 2000-01-01 16:00:00
(записей: 6)
Now I want to join these using number and then calculate the average of
both von columns:
datetest=# select * from (select (select count(1) from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as table1 join (select (select count(1) + 1 from (select von
from daten union select bis as von from daten) as d1 where d1.von <
d2.von) as number, von from (select von from daten union select bis as
von from daten) d2) as table2 using (number);
ERROR: ExecEvalExpr: unknown expression type 108
Is that a bug?
Thanks
--
Markus Bertheau
Cenes Data GmbH
Berlin, Germany
