Hi,
making up my suggestions given my understanding of the desired semantics:
for the orignal question:
create table a (id int, sales int, date timestamp);
create table b (id int, goal int, date timestamp);
select a.*, b.* from a,b, (select a.date as a_date, max(b.date) as b_date from a,b where b.date < a.date group by a.date) as ab where a.date = ab.a_date and b.date = ab.b_date;
for the question below:
create table event_table (yr int, person_id int, svcmon int, payload string);
create table person_table (yr int, person_id int, svcmon int, payload string);
select * from (select e.yr, e.person_id, e.svcmon as e_svcmon, e.payload as e_payload, p.svcmon as p_svcmon, p.payload as p_payload, row_number() over (partition by e.yr,e.person_id order by abs(e.svcmon - p.svcmon) asc) as pos from event_table e , person_table p where e.yr = p.yr and e.person_id = p.person_id) as ep where pos = 1;
knowing actual schemas would help to understand whether "each event" is identified by yr,person_id
(as I assume above) or by, say, (yr,person_id,svcmon) (in which case e.svcmon should be added to
the partition-by clause).
knowing actual schemas might also help to pull the projection out of the inner query,
as thus shrinking the intermediate result size(s) ...
Best,
Stefan
----- On Jul 17, 2015, at 3:27 PM, Anthony Damico ajdamico@gmail.com wrote:
> hi Stefan, i just made up those column names as a simple fake example. i'm not
> sure why schema would help here? it's mostly just a re-statement of the
> original question i linked to in my post..? i guess i could make you some fake
> data to play with, but i'm confused why this query requires table structure?
> sorry if i'm overlooking something silly
>
>
>
>
> On Fri, Jul 17, 2015 at 9:22 AM, Stefan Manegold < Stefan.Manegold@cwi.nl >
> wrote:
>
>
> Anthony,
>
> could you possibly share the full schemas of your "event_table" and
> "person_table",
> including primary keys and foreign keys?
>
> Thanks!
>
> Stefan
>
> ----- On Jul 16, 2015, at 1:57 PM, Anthony Damico ajdamico@gmail.com wrote:
>
>> hi, sorry, partition by event somehow? i'm not seeing it :/
>>
>> On Wed, Jul 15, 2015 at 6:52 PM, Ying Zhang < Y.Zhang@cwi.nl > wrote:
>>
>>
>> Opes, I see. I’m sorry. Then LIMIT 1 is not what you need. MonetDB doesn’t have
>> anything directly assemble TOP 1.
>>
>> Your use case sounds a bit similar to another use case on the mailing list,
>> about computing the OHLC chart.
>> It’s a bit too late to think of the exact query, but how about using PARTION BY,
>> then ROWNUMBER, en select the row with number 0 (or is 1 the starting value?)?
>>
>>> On Jul 16, 2015, at 00:27 , Anthony Damico < ajdamico@gmail.com > wrote:
>>>
>>> hi Jennie, thanks for spending time on this! won't LIMIT only give one record? i
>>> need one record per event.. so i think the "insert into" command would need to
>>> be repeatedly called until every single event got stored (which would take
>>> forever)? sorry if i'm missing something
>>>
>>> On Wed, Jul 15, 2015 at 6:20 PM, Ying Zhang < Y.Zhang@cwi.nl > wrote:
>>> Maybe I’m thinking in a too simple way, but if you store the intermediate
>>> results in a table, you can use LIMIT 1:
>>>
>>> CREATE TABLE t1 (svcmon int); -- assume svcmon is an INT
>>> insert into t1
>>> SELECT svcmon
>>> FROM person_table AS z
>>> WHERE a.yr = z.yr AND a.person_id = z.person_id
>>> ORDER BY abs( z.svcmon - a.svcmon )
>>> LIMIT 1;
>>>
>>> Using TEMP TABLE might give you a bit speed up, but then you need to put them in
>>> one transaction.
>>>
>>> Jennie
>>>
>>> > On Jun 13, 2015, at 07:47 , Anthony Damico < ajdamico@gmail.com > wrote:
>>> >
>>> > any ideas on this? thanks!
>>> >
>>> > On Thu, Jun 4, 2015 at 7:39 AM, Anthony Damico < ajdamico@gmail.com > wrote:
>>> > https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or-match-by-the-nearest-value-without-a-top-or-lim
>>> >
>>> > i'm thinking i can do this with some costly self-join, but i'd appreciate any
>>> > other eyes on the problem
>>> >
>>> > _______________________________________________
>>> > users-list mailing list
>>> > users-list@monetdb.org
>>> > https://www.monetdb.org/mailman/listinfo/users-list
>>>
>>> _______________________________________________
>>> users-list mailing list
>>> users-list@monetdb.org
>>> https://www.monetdb.org/mailman/listinfo/users-list
>>>
>>> _______________________________________________
>>> users-list mailing list
>>> users-list@monetdb.org
>>> https://www.monetdb.org/mailman/listinfo/users-list
>>
>> _______________________________________________
>> users-list mailing list
>> users-list@monetdb.org
>> https://www.monetdb.org/mailman/listinfo/users-list
>>
>>
>> _______________________________________________
>> users-list mailing list
>> users-list@monetdb.org
>> https://www.monetdb.org/mailman/listinfo/users-list
>
> --
>| Stefan.Manegold@CWI.nl | DB Architectures (DA) |
>| www.CWI.nl/~manegold/ | Science Park 123 (L321) |
>| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
--
| Stefan.Manegold@CWI.nl | DB Architectures (DA) |
| www.CWI.nl/~manegold/ | Science Park 123 (L321) |
| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list