nearest value question on stackoverflow

Anthony Damico

4 Jun 2015 4 Jun '15

11:39 a.m.

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or... i'm thinking i can do this with some costly self-join, but i'd appreciate any other eyes on the problem

Attachments:

attachment.html (text/html — 419 bytes)

Show replies by date

Anthony Damico

13 Jun 13 Jun

5:47 a.m.

any ideas on this? thanks! On Thu, Jun 4, 2015 at 7:39 AM, Anthony Damico wrote:

...

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

i'm thinking i can do this with some costly self-join, but i'd appreciate any other eyes on the problem

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 wrote:

any ideas on this? thanks!

On Thu, Jun 4, 2015 at 7:39 AM, Anthony Damico wrote: https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

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

Anthony Damico

10:27 p.m.

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 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 wrote:

any ideas on this? thanks!

On Thu, Jun 4, 2015 at 7:39 AM, Anthony Damico wrote:

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

...
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

Ying Zhang

10:52 p.m.

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 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 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 wrote:

any ideas on this? thanks!

On Thu, Jun 4, 2015 at 7:39 AM, Anthony Damico wrote: https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

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

Anthony Damico

16 Jul 16 Jul

11:57 a.m.

hi, sorry, partition by event somehow? i'm not seeing it :/ On Wed, Jul 15, 2015 at 6:52 PM, Ying Zhang 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 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 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 wrote:

any ideas on this? thanks!

On Thu, Jun 4, 2015 at 7:39 AM, Anthony Damico wrote:

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

...
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

Stefan Manegold

17 Jul 17 Jul

1:22 p.m.

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...

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

Anthony Damico

1:27 p.m.

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 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...

...
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

Stefan Manegold

2:15 p.m.

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...

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

Anthony Damico

2:42 p.m.

that's amazing, thank you!! i've posted here as well- https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or... On Fri, Jul 17, 2015 at 10:15 AM, Stefan Manegold wrote:

...

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...

...
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

Eyal Rozenberg

2:39 p.m.

This is probably more of a question than an answer, but - why wouldn't this work?: CREATE TABLE nearest_matches AS ( SELECT a.* , ( SELECT min(abs(z.svcmon - a.svcmon)) FROM person_table AS z WHERE a.yr = z.yr AND a.person_id = z.person_id ) AS nearest_month FROM event_table AS a ) WITH DATA (... that is, aggregation instead of TOP 1.) Eyal On 04/06/2015 14:39, Anthony Damico wrote:

...

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

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

Anthony Damico

2:49 p.m.

hi Eyal, that does tell you the minimum distance, but not the month which is the minimum distance, i believe? so if your month is july, you might know the nearest month is august or june, but not which one. On Fri, Jul 17, 2015 at 10:39 AM, Eyal Rozenberg wrote:

...

This is probably more of a question than an answer, but - why wouldn't this work?:

CREATE TABLE nearest_matches AS ( SELECT a.* , ( SELECT min(abs(z.svcmon - a.svcmon)) FROM person_table AS z WHERE a.yr = z.yr AND a.person_id = z.person_id ) AS nearest_month FROM event_table AS a ) WITH DATA

(... that is, aggregation instead of TOP 1.)

Eyal

On 04/06/2015 14:39, Anthony Damico wrote:

...
https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

...
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

Stefan Manegold

2:51 p.m.

because one wants the actual z.svcmon value in the result, not it's abs.distance to a.svcmon ;-) ----- On Jul 17, 2015, at 4:39 PM, Eyal Rozenberg eyalroz@technion.ac.il wrote:

...

This is probably more of a question than an answer, but - why wouldn't this work?:

CREATE TABLE nearest_matches AS ( SELECT a.* , ( SELECT min(abs(z.svcmon - a.svcmon)) FROM person_table AS z WHERE a.yr = z.yr AND a.person_id = z.person_id ) AS nearest_month FROM event_table AS a ) WITH DATA

(... that is, aggregation instead of TOP 1.)

Eyal

On 04/06/2015 14:39, Anthony Damico wrote:

...
https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-or...

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

3451

Age (days ago)

3494

Last active (days ago)

List overview

Download

12 comments

4 participants

participants (4)

Anthony Damico
Eyal Rozenberg
Stefan Manegold
Ying Zhang