joinable generate_series with arguments from a table
Stefan de Konink
stefan at konink.de
Tue Jun 12 09:24:35 CEST 2018
Goodmorning,
At this moment there is no functional compatibility between the PostgreSQL
and MonetDB with respect to the generate_series function. The range in
PostgreSQL is inclusive, in MonetDB exclusive. More importantly
generate_series can't be used with argument that is joined from a different
table, MonetDB just seems to support a subquery, hence joining that back
without a primary is impossible.
PostgreSQL;
select generate_series(a, b), a from (select 1 as a, 2 as b) as a;
generate_series | a
-----------------+---
1 | 1
2 | 1
(2 rows)
MonetDB;
select * from generate_series((select 1 as a, 2 as b));
+-------+
| value |
+=======+
| 1 |
+-------+
If I would like to functionally achieve the following;
I have list that states the speeds over partial segments, identified by
linear referencing. From 10 meter to 12 meter, the distance was 2 meter and
speed was 2s.
10 2 2
I would like to expand these values to segments of one meter, hence end up
with a table that would state:
10 2 2
11 2 2
12 2 2
Now I am aware of the Python bindings and I could achieve what I wanted by
the function below, but I would find generate_series still very practical.
DROP FUNCTION expand_per_meter;
CREATE FUNCTION expand_per_meter(trip_hash BIGINT, userstopcode STRING,
distancesincelastuserstop INTEGER, s INTEGER, t INTEGER)
RETURNS TABLE(trip_hash BIGINT, userstopcode STRING,
distancesincelastuserstop INTEGER, s INTEGER, t INTEGER)
LANGUAGE PYTHON {
result = dict()
result['trip_hash'] = []
result['userstopcode'] = []
result['distancesincelastuserstop'] = []
result['s'] = []
result['t'] = []
for i in range(0, len(trip_hash)):
expanded = range(distancesincelastuserstop[i],
distancesincelastuserstop[i] + s[i] + 1)
expand = len(expanded)
result['trip_hash'] += [trip_hash[i]] * expand
result['userstopcode'] += [userstopcode[i]] * expand
result['distancesincelastuserstop'] += expanded
result['s'] += [s[i]] * expand
result['t'] += [t[i]] * expand
return result
};
--
Stefan
More information about the users-list
mailing list