This produces the equivalent SELECT ... UNION ALL ... UNION ALL ...
using select, literal_column and union_all (and leaving aside the
details of representing the literal values inside the VALUES clause)
--- works just fine and PostgreSQL produces the same query plan for
it:
num_columns = len(weights_and_valueids[0]) # assuming all are the same
length
weights_clause = union_all(*(
select([literal_column('%s' % weight_and_valueid[i]).label('column
%d' % (i+1))
for i in xrange(0, num_columns)
])
for weight_and_valueid in weights_and_valueids
)).alias('weights')
Is this more portable, and thus should be used in preference to VALUES
as a literal table clause?
Regards,
- Gulli
On Feb 26, 10:28 am, Gunnlaugur Briem <gunnlau...@gmail.com> wrote:
> 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
> herehttp://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.
