Thanks for the follow-up. Hmm, yes, PostgreSQL 8.x was lacking quite a bit of functionality compared to more up-to-date versions.
Looking forward to learn how your analyses turn out! Cheers Lukas 2014-07-11 11:47 GMT+02:00 <[email protected]>: > i am on redshift which is postgres 8.x. so materialized views are out. > i am reading up on window functions and have ordered the book as well. > will take a stab at the problem again after finishing the reading part. > > Thanks, > Gokul > > > On Thursday, July 10, 2014 11:45:33 AM UTC+5:30, Lukas Eder wrote: > >> Hi Gokul, >> >> Maybe, Witold referred to materialized views, which might indeed be a >> viable option if you're using PostgreSQL 9.3+: >> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html >> >> This materialized view approach is quite similar to your temp table >> approach. Both are fine, I think. >> >> Now, I also understand your previous questions - I think that your column >> naming approach is fine. You also have an option to use runtime table >> mapping to map the original table to the temp table name in rendered SQL, >> though. This is documented here: >> http://www.jooq.org/doc/latest/manual/sql-building/ >> dsl-context/runtime-schema-mapping/ >> >> However, looking at your second query, I suspect you might be able to >> avoid it completely and calculate that COUNT() value already in your first >> query using window functions: >> >> COUNT(COURSE_STATUS.COMPLETION_DATE) OVER(PARTITION BY >> COURSE_STATUS.COURSE_ID) >> >> >> I suggest reading this post for more details: >> http://blog.jooq.org/2013/11/03/probably-the-coolest-sql- >> feature-window-functions/ >> >> Another thing that comes to mind is proper indexing. You're expressing a >> predicate like so: >> >> extract(COURSE_STATUS.ENROLLED_DATE,DatePart.YEAR).eq(2014) >> >> >> Do you have an function-based index on the relevant expression? If not, >> maybe you should add one, or pre-calculate that column using a trigger, and >> then filter on that pre-calculated (obviously, indexed) column. However, >> optimal indexing is non-trivial if you allow random predicates to be >> applied. But if you have a quite selective predicate that is always >> applied, you should make sure that it can profit from an index. >> >> We usually recommend reading Markus Winand's excellent book on SQL >> Performance: >> http://sql-performance-explained.com/l >> >> You can use the "jOOQ" discount code to get a 10% discount on books >> ordered over this website. >> >> That should be food for thought :-) >> >> Cheers, >> Lukas >> >> 2014-07-10 7:41 GMT+02:00 <[email protected]>: >> >> The view would not be able to cache the results of the first expensive >>> query. >>> >>> Thanks, >>> Gokul >>> >>> >>> On Thursday, July 10, 2014 5:37:23 AM UTC+5:30, Witold Szczerba wrote: >>> >>>> Maybe it would be possible to create a view and query it instead? >>>> >>>> Regards, >>>> Witold Szczerba >>>> --- >>>> Sent from my mobile phone. >>>> On Jul 9, 2014 2:22 PM, <[email protected]> wrote: >>>> >>>>> Hi, >>>>> >>>>> A little background. The problem i am solving is a bit similar to this >>>>> simplified version. >>>>> >>>>> As an admin of a mooc site you want to track how many people have >>>>> completed, started in enrolled in various course enrollments over the past >>>>> 1 year and plot them in a single graph >>>>> >>>>> for each user, course a record is present in a course status table >>>>> which has user id,course id,enrolled date,started date, completed date etc >>>>> >>>>> SelectConditionStep<Record> selectStmnt = create.select(COURSE_STATUS. >>>>> USER_ID,COURSE_STATUS.COURSE_ID,COURSE_STATUS.STARTED_DATE,CO >>>>> URSE_STATUS.COMPLETED_DATE,COURSE_STATUS.ENROLLED_DATE) >>>>> .from(COURSE_STATUS) >>>>> .where(extract(COURSE_STATUS.ENROLLED_DATE,DatePart.YEAR).eq(2014)); >>>>> >>>>> dslContext.execute("CREATE TEMP TABLE TempTable as {0}", selectStmnt); >>>>> >>>>> dslContext.select( >>>>> field(COURSE_STATUS.COURSE_ID.getName()), DSL.count()) >>>>> .from("TempTable") >>>>> .where(field(COURSE_STATUS.COMPLETION_DATE.getName()). >>>>> isNotNull()) >>>>> .groupBy(field(COURSE_STATUS.COURSE_ID.getName())) >>>>> .fetch(); >>>>> >>>>> So on for started, enrolled etc. >>>>> >>>>> The System has lot more filteration params like location,sex,age group >>>>> etc and more data points can be viewed as passed the course,failed it, etc >>>>> >>>>> Thanks, >>>>> Gokul >>>>> >>>>> >>>>> >>>>> >>>>> On Wednesday, July 9, 2014 4:43:34 PM UTC+5:30, Lukas Eder wrote: >>>>>> >>>>>> Hi Gokul, >>>>>> >>>>>> It's hard to say from your description. Maybe, share a little code to >>>>>> see where you're going and to see whether that's a good idea? >>>>>> >>>>>> Cheers >>>>>> Lukas >>>>>> >>>>>> 2014-07-09 12:57 GMT+02:00 <[email protected]>: >>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> I figured out a way in which i call getName on the original >>>>>>> generated column and do a select on the new table. That seems to work >>>>>>> well >>>>>>> for post gres. >>>>>>> I am very new to sql so i am not sure if this is a best practice of >>>>>>> using only the column name without the schema and table names. >>>>>>> >>>>>>> Thanks, >>>>>>> Gokul >>>>>>> >>>>>>> >>>>>>> On Wednesday, July 9, 2014 4:10:10 PM UTC+5:30, [email protected] >>>>>>> wrote: >>>>>>>> >>>>>>>> Thanks Lukas. >>>>>>>> That fills the bill nicely. >>>>>>>> >>>>>>>> Is there any way i can use the earlier jooq generated column names >>>>>>>> to refer to the columns names in the table or should i use sql builder >>>>>>>> with strings to query this table. >>>>>>>> >>>>>>>> Thanks, >>>>>>>> Gokul >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Wednesday, July 9, 2014 3:26:41 PM UTC+5:30, Lukas Eder wrote: >>>>>>>>> >>>>>>>>> Hello, >>>>>>>>> >>>>>>>>> This is currently not supported by jOOQ and we probably won't >>>>>>>>> support it as PostgreSQL documentation recommends using the CREATE >>>>>>>>> TABLE AS >>>>>>>>> syntax instead of SELECT INTO. >>>>>>>>> >>>>>>>>> Note that other databases including the SQL standard specify >>>>>>>>> SELECT INTO as follows: >>>>>>>>> >>>>>>>>> <select statement: single row> ::= >>>>>>>>> SELECT [ <set quantifier> ] <select list> >>>>>>>>> INTO <select target list> >>>>>>>>> <table expression> >>>>>>>>> >>>>>>>>> <select target list> ::= >>>>>>>>> <target specification> [ { <comma> <target specification> }... ] >>>>>>>>> >>>>>>>>> >>>>>>>>> So, INTO expects column references or parameters, for instance, >>>>>>>>> not table references. >>>>>>>>> >>>>>>>>> Now, CREATE TABLE AS can be used with jOOQ using plain SQL as >>>>>>>>> follows: >>>>>>>>> >>>>>>>>> DSL.using(configuration) >>>>>>>>> >>>>>>>>> .execute("CREATE TABLE xx AS {0}", select); >>>>>>>>> >>>>>>>>> >>>>>>>>> Where select is your jOOQ SELECT statement. >>>>>>>>> >>>>>>>>> In any case, the CREATE TABLE AS statement would be a useful >>>>>>>>> addition to the newly introduced set of DDL statements in jOOQ. I have >>>>>>>>> added it to the roadmap for jOOQ 3.5: >>>>>>>>> https://github.com/jOOQ/jOOQ/issues/3381 >>>>>>>>> >>>>>>>>> Cheers >>>>>>>>> Lukas >>>>>>>>> >>>>>>>>> 2014-07-09 11:16 GMT+02:00 <[email protected]>: >>>>>>>>> >>>>>>>>>> Hi, >>>>>>>>>> >>>>>>>>>> postgres has a select into which allows defining a new table from >>>>>>>>>> the results of a query. Is there any way i can do this in jooq? >>>>>>>>>> >>>>>>>>>> http://www.postgresql.org/docs/9.3/static/sql-selectinto.html >>>>>>>>>> >>>>>>>>>> i have a use case where i create a complex query containing a >>>>>>>>>> number of joins and a number of conditions to get a result set. >>>>>>>>>> On this result set i need to compute different aggregate values >>>>>>>>>> which are then used for plotting a graph. >>>>>>>>>> >>>>>>>>>> I thought it would be more efficent to push the results of the >>>>>>>>>> first query into a temp table and then do the later computation on >>>>>>>>>> the >>>>>>>>>> temporary table. >>>>>>>>>> >>>>>>>>>> Thanks, >>>>>>>>>> Gokul >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> You received this message because you are subscribed to the >>>>>>>>>> Google Groups "jOOQ User Group" group. >>>>>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>>>>> send an email to [email protected]. >>>>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "jOOQ User Group" group. >>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>> send an email to [email protected]. >>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>> >>>>>> >>>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "jOOQ User Group" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to [email protected]. >>>>> For more options, visit https://groups.google.com/d/optout. >>>>> >>>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
