我有一张这样的桌子
CREATE TABLE public.userlocation ( datetime timestamp with time zone, location geometry, locationtype integer, buffer double precision, timebuffer double precision, "userID" numeric, "ID" integer NOT NULL DEFAULT nextval('"userlocation_ID_seq"'::regclass), "time" time with time zone )每行有一个时间，然后是一个值来制作动态时间范围，实际上如果time=8且time=8 timebuffer=15那么时间范围将是endTime= 8+15min和startTime= 8-15 min 。我可以这样做只需使用这个查询
select f1.*,f1.time +(f1.timebuffer::text||' minute')::INTERVAL as startTime,f1.time-(f1.timebuffer::text||' minute')::INTERVAL as endTime一切正常，在这个阶段之后，我想查询它们的时间段在startTime和endTime之间的行，而在其他单词中它们重叠。 我已经发现了这个问题
PostgreSQL查询检测重叠的时间范围
但是这里有区别，我没有开始时间和结束时间所以我必须使用上面的方法创建它们。 所以野兔是我的疑问
select f1.*,f1.time -(f1.timebuffer::text||' minute')::INTERVAL as startTime,f1.time+(f1.timebuffer::text||' minute')::INTERVAL as endTime from userlocation f1 where exists (select f2.time -(f2.timebuffer::text||' minute')::INTERVAL as startTime,f2.time+(f2.timebuffer::text||' minute')::INTERVAL as endTime from userlocation f2 where tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()') and f2.locationtype = f1.locationtype and f2.locationtype=1 and f2."ID" <> f1."ID");但是我得到了这个错误
[2016-08-27 23:42:45] [42703] ERROR: column f2.starttime does not exist职位：372
我想起初我应该创建F2表，但我不知道怎么样，你能给我一些提示吗？
I have a table like this
CREATE TABLE public.userlocation ( datetime timestamp with time zone, location geometry, locationtype integer, buffer double precision, timebuffer double precision, "userID" numeric, "ID" integer NOT NULL DEFAULT nextval('"userlocation_ID_seq"'::regclass), "time" time with time zone )each row has a time and then a value to make a dynamic time range, in fact If time=8 and timebuffer=15so time range would be endTime= 8+15min and startTime= 8-15 min.I can do this simply using this query
select f1.*,f1.time +(f1.timebuffer::text||' minute')::INTERVAL as startTime,f1.time-(f1.timebuffer::text||' minute')::INTERVAL as endTimeeverything work fine, After this stage I want to query rows that their time period is between startTime and endTime in other word they overlap. I have already found this question
PostgreSQL query to detect overlapping time ranges
But there is a difference here,I don't have start time and endtime so I have to create them using above method. so hare is my query
select f1.*,f1.time -(f1.timebuffer::text||' minute')::INTERVAL as startTime,f1.time+(f1.timebuffer::text||' minute')::INTERVAL as endTime from userlocation f1 where exists (select f2.time -(f2.timebuffer::text||' minute')::INTERVAL as startTime,f2.time+(f2.timebuffer::text||' minute')::INTERVAL as endTime from userlocation f2 where tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()') and f2.locationtype = f1.locationtype and f2.locationtype=1 and f2."ID" <> f1."ID");But I get this error
[2016-08-27 23:42:45] [42703] ERROR: column f2.starttime does not existPosition: 372
I think At first I should create F2 table but I dont know how,Can you please give me some hints?
最满意答案
第一：列别名（ select expression AS somename ） 在其查询中不可用，它只能从查询外部看到。 您可以通过将其包装到(subquery) xx或视图或CTE中来解决此问题
第二：不要重复自己：如果你需要计算两次相同的表达式，你可能做得太多了......
CREATE TEMP VIEW omg AS SELECT fx.*,fx.time -(fx.timebuffer::text||' minute')::INTERVAL as startTime ,fx.time+(fx.timebuffer::text||' minute')::INTERVAL as endTime , fx.locationtype , fx.ID -- ... maybe more columns and expressions ... FROM userlocation fx ; SELECT f1.startTime, f1.endTime -- ... maybe more columns and expressions ... FROM omg f1 WHERE EXISTS ( SELECT 1 FROM omg f2 WHERE tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()') AND f2.locationtype = f1.locationtype AND f2.locationtype=1 AND f2."ID" <> f1."ID") ; 而不是视图，你可以使用CTE（视图可能表现更好） 你也许可以将tsrange拉入视图或CTE 我没有检查逻辑
为了完整性，CTE版本（几乎看起来相同）
WITH omg AS ( SELECT fx.*,fx.time -(fx.timebuffer::text||' minute')::INTERVAL as startTime ,fx.time+(fx.timebuffer::text||' minute')::INTERVAL as endTime , fx.locationtype , fx.ID -- ... maybe more columns and expressions ... FROM userlocation fx ) SELECT f1.startTime, f1.endTime -- ... maybe more columns and expressions ... FROM omg f1 WHERE EXISTS ( SELECT 1 FROM omg f2 WHERE tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()') AND f2.locationtype = f1.locationtype AND f2.locationtype=1 AND f2."ID" <> f1."ID") ;First: The column-alias (select expression AS somename) is not usable from within its query, it is only visible from outside the query. You can solve this by wrapping it into a (subquery) xx or a view or a CTE
Second: don't repeat yourself: if you need to compute the same expression(s) twice, you could be doing too much ...
CREATE TEMP VIEW omg AS SELECT fx.*,fx.time -(fx.timebuffer::text||' minute')::INTERVAL as startTime ,fx.time+(fx.timebuffer::text||' minute')::INTERVAL as endTime , fx.locationtype , fx.ID -- ... maybe more columns and expressions ... FROM userlocation fx ; SELECT f1.startTime, f1.endTime -- ... maybe more columns and expressions ... FROM omg f1 WHERE EXISTS ( SELECT 1 FROM omg f2 WHERE tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()') AND f2.locationtype = f1.locationtype AND f2.locationtype=1 AND f2."ID" <> f1."ID") ; instead of a view you could use a CTE (the view probably performs better) you could probably pull the tsrange into the view or CTE, too I didn't check the logic
For completeness, the CTE version (which almost looks the same)
WITH omg AS ( SELECT fx.*,fx.time -(fx.timebuffer::text||' minute')::INTERVAL as startTime ,fx.time+(fx.timebuffer::text||' minute')::INTERVAL as endTime , fx.locationtype , fx.ID -- ... maybe more columns and expressions ... FROM userlocation fx ) SELECT f1.startTime, f1.endTime -- ... maybe more columns and expressions ... FROM omg f1 WHERE EXISTS ( SELECT 1 FROM omg f2 WHERE tsrange(f2.startTime, f2.endTime, '()') && tsrange(f1.startTime, f1.endTime, '()') AND f2.locationtype = f1.locationtype AND f2.locationtype=1 AND f2."ID" <> f1."ID") ;更多推荐
发布评论