Literal table expression (VALUES clause) in select
Gunnlaugur Briem
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
Gunnlaugur Briem
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
Michael Bayer
On Feb 26, 2010, at 6:28 AM, Gunnlaugur Briem wrote:
> 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?
ah the literal values clause, just had to use that recently. Probably should start cataloging recipes for these things:
from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import FromClause
from sqlalchemy.sql import table, column
class values(FromClause):
def __init__(self, *args):
self.list = args
def _populate_column_collection(self):
self._columns.update(
[("column%d" % i, column("column%d" % i))
for i in xrange(1, len(self.list[0]) + 1)]
)
@compiles(values)
def compile_values(element, compiler, asfrom=False, **kw):
v = "VALUES %s" % ", ".join(
"(%s)" % ", ".join(repr(elem) for elem in tup)
for tup in element.list
)
if asfrom:
v = "(%s)" % v
return v
if __name__ == '__main__':
t1 = table('t1', column('a'), column('b'))
t2 = values((1, 0.5), (2, -0.5)).alias('weights')
print select([t1, t2]).select_from(t1.join(t2, t1.c.a==t2.c.column2))
I will log a ticket to add a nicely subclassable/documented "FromElement" since that part above is non-obvious.
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>