Re: Joining time fields?
От | James David Smith |
---|---|
Тема | Re: Joining time fields? |
Дата | |
Msg-id | CAMu32ADqrcgSwqnh7fAFq1yRuOoiDXL=o1x4y5Ue9O=-DOhDow@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Joining time fields? ("Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>) |
Список | pgsql-novice |
Afternoon,
Hmm. If I use INNER JOIN then the rows that have data from table A but not from table B are not present any more. This isn't correct. I want the rows from table A to be there. They want joining with the nearest (in time) data from table B.
To clarify, every row from table A should have a join with table B. The query should take each row of table A, and then find the nearest time in table B. If the query finds two records in table B that are both exactly the same time away from the record in table A (this won't happen very often), then the record in table A should be duplicated and both records from table B joined to each of them respectively.
Does that make sense!?
James
On 26 July 2012 15:14, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
Howdy, James,The line takes into account negative differences, the sgn is for that.On my test case I actually din't have the situation you described.But I notice that you are using LEFT JOIN instead of INNER JOIN. Can that be the cause for the empty fields?Best,Oliveiros----- Original Message -----From: James David SmithSent: Thursday, July 26, 2012 2:40 PMSubject: Re: [NOVICE] Joining time fields?Dear Oliveiros,Thanks for your reply. I've been working on this today, but don't think that the query is quite right. On this line of the query:// ON y."date_time" = x."date_time" - (sgn*dist )I think that we also need to take into account a positive difference as well as a negative? I've had a play around with your query and put all my proper field names in. I think it works. it returns the number of rows that I would expect. The only odd thing is that the first 20-30 rows of data, which represent the earliest data_time in table A, do not have any data linked to them in table B. Just empty cells. Your thoughts are appreciated!SELECT
x."id" as id,
x."person" as person,
x."the_geom_osgb36" as location,
x."date_time" as gps_time,
y."date_time" as microaeth_time,
y."bc" as black_carbon
FROM(SELECT
a."id",
a."date_time",
a.person,
a.the_geom_osgb36,
MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgnFROM gps_12_07_2012 a, microaeth_12_07_2012 bWHERE a.person = 'Ben Barratt'
GROUP by
a."id",
a."date_time",
a.person,
a.the_geom_osgb36
) xLEFT JOIN (SELECT * FROM microaeth_12_07_2012 WHERE person = 'Ben Barratt') yON y."date_time" = x."date_time" - (sgn*dist)
OR
y."date_time" = x."date_time" + (sgn*dist)ORDER BY x.id, x.date_time
On 25 July 2012 12:08, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:Hello again, James,I got no feedback from you, but I believe this query is flawed.Also, I believe you need the other fields from your tables and not just the TIMESTAMP fields.So, considering your tables have fields like table_one.col_on_one and table_two.col_on_two,I'd try this querySELECT x."col_on_one", x."date_time",y."col_one_two",y."date_time"
FROM(
SELECT a."col_on_one",a."date_time", MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
FROM table_one a,table_two b
GROUP by a."col_on_one",a."date_time"
) x
INNER JOIN t_table_two y
ON y."date_time" = x."date_time" - (sgn*dist )If it doesn't work, tell me the error it reported and we'll try to fix itBest,Oliver----- Original Message -----Sent: Tuesday, July 24, 2012 5:20 PMSubject: Re: [NOVICE] Joining time fields?Hi again,James,This is untested code. Can you see if it works?The trouble is that if you have giant tables it will become slow...Best,OliveirosSELECT date_time_in_a, d.date_time as date_time_in_bFROM(SELECT a.date_time as date_time_in_a, MIN(a.date_time - b.date_time) as distFROM table_one a, table_two bGROUP BY a.date_time) cJOINtable_two dON c.dist - c.date_time_in_a = d.date_time----- Original Message -----From: James David SmithSent: Tuesday, July 24, 2012 4:33 PMSubject: Re: [NOVICE] Joining time fields?Hi Oliveiros,Thanks for your time. It's an inner join then hey instead of a left join? Ok, thanks.In your example of using one record inn table A, and two records in table B that are exactly the same, I would like the result to be a new table with two records in it. I'm happy for the result of the query to duplicate records from table A.Yes, even if the nearest time is 100 years away I would still like the query to get the right result. There is no limit to how far the 'nearest' time is.Thank youJames
On 24 July 2012 16:25, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:Hi, James,But that wouldn't be a LEFT JOIN, it will be an INNER JOIN.Because you'll always be able to join a date from table a with some date from table b even if it is 100 years away...If table a has just one record datetime = 2012-1-1 and table b has two records datetime = 2010-1-1 and datetime = 2011-1-1 then you'd be able to join table a with the second of table b' records.You won't be able to join only if table b happens to be empty...ain't I right?What do you mean by the closest time? Do you have some threshold ? Are they allowed to be arbitrarily far away one from each other?Best,Oliveiros----- Original Message -----From: James David SmithSent: Tuesday, July 24, 2012 3:57 PMSubject: [NOVICE] Joining time fields?Hi all,I wonder if someone could help me out please. I've got two tables, both with a TIMESTAMP field. I'd like to do a left join with them. I'd like to take the date_time from table A, and join it with the nearest date_time from table B. Whether the time from B is before or after the time in A doesn't matter, I just want the closest time. I started with the below query, but it only gets me the column from table B if the time stamp exactly matches which is clearly correct. I'm sure that this should be quite easy but I can't figure it out...!Select
a.date_time
b.date_time
FROM table_one a
LEFT JOIN table_two b ON a.date_time = b.date_time
Thanks
James
В списке pgsql-novice по дате отправления: