Oh I didn't know that, I'm use this code to run on Redshift so I mainly
refer to AWS Redshift docs because not all modern PostgreSQL features are
supported. And I didn't realize CREATE TABLE AS is superset of SELECT INTO.
The good news your example works on Redshift too so it's fully supported
there as well. Thanks again.
On Thursday, November 8, 2018 at 7:20:28 PM UTC+2, Jeremy Evans wrote:
>
> On Thursday, November 8, 2018 at 8:39:05 AM UTC-8, Troex Nevelin wrote:
>>
>> Hi, I couldn't find a way to write SELECT * INTO query, I've basically a
>> case where I need to use WITH and JOIN both when inserting into table, I'm
>> not "pro" in SQL but as far as I know the only way to implement it using is
>> SELECT INTO statement. I've been working tightly with sequel past two years
>> building complex queries but this is first time I'm really stuck.
>>
>> Here is full working query I'm trying rewrite into sequel
>>
>> WITH "visits" AS
>> (SELECT "filter",
>> COUNT("id") AS "visit_count",
>> CAST(CONVERT_TIMEZONE('America/Los_Angeles', "visit_time") AS
>> date) AS "visit_date"
>> FROM "visits_1"
>> GROUP BY "filter",
>> "visit_date")
>> SELECT ("value"::float / "visit_count") AS "cpv",
>> "visit_count",
>> "visit_date",
>> "filter",
>> "value" AS "amount"
>> INTO "costs_1"
>> FROM "visits"
>> INNER JOIN "amounts" ON (("amounts"."filter_id" = "visits"."filter")
>> AND ("amounts"."start_date" = "visits".
>> "visit_date"))
>> WHERE ("project_id" = 1)
>> ;
>>
>> And here is ruby code I currently have, which generates INSERT statement
>> which doesn't work
>>
>> project_id ||= 1
>> timezone ||= 'America/Los_Angeles'
>> visits_table = :"visits_#{project_id}"
>> table_name = :"costs_#{project_id}"
>>
>>
>> visits = DB[visits_table].
>> select(
>> :filter,
>> Sequel.lit('COUNT(?)', :id).as(:visit_count),
>> Sequel.lit('CONVERT_TIMEZONE(?, ?)', timezone, :visit_time).cast(:
>> date).as(:visit_date)
>> ).
>> group(:filter, :visit_date)
>>
>>
>> query = DB[:visits].
>> # with(:visits, visits). # moved to insert query
>> select(
>> Sequel.lit('(?::float / ?)', :value, :visit_count).as(:cpv),
>> :visit_count,
>> :visit_date,
>> :filter,
>> Sequel.as(:value, :amount)
>> ).
>> join(:amounts, { filter_id: :filter, start_date: :visit_date }).
>> where(project_id: project_id)
>>
>>
>> DB[table_name].
>> with(:visits, visits).
>> insert_sql(query)
>>
>>
>> Probably the solution might be somewhere around here
>> https://github.com/jeremyevans/sequel/blob/51d96b8700df2c4cae9963bd90428efc92755e43/lib/sequel/adapters/shared/postgres.rb#L1361
>>
>> I think I've seen something about SELECT INTO in MSSQL adapter code.
>>
>> I think my query can be rewritten without using SELECT INTO, but some
>> pieces of the ruby code are shared across my app and I'l like to keep these
>> business logic pieces "shared". Any advice or solution is welcome.
>>
>>
>> Thank you Jeremy for best gem ever!
>>
>
> As the PostgreSQL documentation states (
> https://www.postgresql.org/docs/current/sql-selectinto.html), SELECT INTO
> is basically the same as CREATE TABLE AS, and CREATE TABLE AS is the
> recommended syntax. Sequel supports CREATE TABLE AS via:
>
> DB.create_table(:costs_1, :as=>DB[:visits].
> join(:amounts, :filter_id=>:filter, :start_date=>:visit_date).
> with(:visits, DB[:visits_1].
> select_group{[:filter, CONVERT_TIMEZONE('America/Los Angeles',
> :visit_time).cast(Date).as(:visit_date)]}.
> select_append{count(:id).as(:visit_count)}
> ).
> where(:project_id=>1).
> select do [
> (value.cast(Float) / visit_count).as(:cpv),
> :visit_count,
> :visit_date,
> :filter,
> value.as(:amount)
> ] end
> )
>
> Which uses the following SQL:
>
> CREATE TABLE "costs_1" AS
> WITH "visits" AS (
> SELECT "filter",
> CAST(CONVERT_TIMEZONE('America/Los Angeles', "visit_time") AS
> date) AS "visit_date",
> count("id") AS "visit_count"
> FROM "visits_1"
> GROUP BY "filter",
> CAST(CONVERT_TIMEZONE('America/Los Angeles', "visit_time") AS
> date)
> )
> SELECT (CAST("value" AS double precision) / "visit_count") AS "cpv",
> "visit_count",
> "visit_date",
> "filter",
> "value" AS "amount"
> FROM "visits"
> INNER JOIN "amounts" ON (("amounts"."filter_id" = "visits"."filter")
> AND ("amounts"."start_date" =
> "visits"."visit_date"))
> WHERE ("project_id" = 1)
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
