Hi,
I want to perform a weighted sum over one column, specifying the
weights in a literal table (a VALUES clause):
select a, sum(b*weights.column1)
FROM (select a, b from whatever) foo
JOIN (values (1, 0.5), (2, -0.5) ) weights ON weights.column2=a
GROUP BY 1;
This is on PostgreSQL 8.4. To represent this in
sqlalchemy.sql.expression terms:
select([subquery.c.a, func.sum(subquery.c.b *
weights_clause.c['column2']),
weights_clause.c['column1']==subquery.c.a
)
but how do I get the weights_clause? I want it to compile to VALUES
(1, 0.5), (2, -0.5) (and then wrap it with .alias('weights')) but it
seems like there is no expression for this in SQLAlchemy so far (just
the VALUES support in INSERT and UPDATE, which does not work for this
use).
Does this require a new subclass of FromClause, or is there an
existing way that I'm not seeing?
The column names 'column1', 'column2' are as specified here
http://www.postgresql.org/docs/8.4/static/queries-values.html — the
names may be specific to PostgreSQL, not sure, but I believe (haven't
checked) that SQL generally allows VALUES to be used wherever SELECT
can be used.
Thanks,
- Gulli
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
