Re: fastes way to retrieve segmented without using loop - Mailing list pgsql-general

Melvin's use of DISTINCT ON (...) is superior to my use of DISTINCT(...) because it doesn't return the value to your program. I keep forgetting this way. I learned it the other way. Old dog + new trick == problem.

On Sun, Aug 23, 2015 at 5:04 PM, John McKown <john.archie.mckown@gmail.com> wrote:

On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith <tomsmith1989sk@gmail.com> wrote:

Hello:

I have a time series table,

using below sql, loop (psque code), I can get one row for each hour

​s/psque/pseudo/g;​

 

for( H=1: H< 99999; H++){

   select  * from table where  t >= H and t < H+1   limit 1

}

t (time column) is indexed).

Is there a better way to use a issue a SINGLE SQL
with  an array of time start/end pair

so it will be executed once to send back result, which would be much faster

than issuing sql again and again (even with prepared statement and using sql function).

Thanks in Advance

Well, I have a bit of a problem if "t" is a "time column". Do you mean a "time interval"? Or do you really mean it is an integer of some sort. I ask because H sure looks like a plain old integer to me. 

In any case, if "t" is an "int" as opposed to a "time interval", then you could start with something like:

SELECT DISTINCT(t), ???? columns ???? FROM table WHERE t BETWEEN 1 AND 99999;

But if "t" really is a "time interval" in the PostgreSQL sense, and H is like the "hour" portion (H --> Hour, makes sense to this weirdo). And you want to select one row of data where the "t" interval is 1 hour, another where the "t" interval is 2 hours, another where the "t" interval is 3 hours, and so on up to an interval of at most 99_999 hours. Then you might need something like:

SELECT DISTINCT(EXTRACT(HOUR FROM t)) AS interval_truncated_to_hour, * FROM table WHERE t BETWEEN 1 AND 99999;

I don't know of a way to eliminate the first field from the result. But, in reality, I would _never_ issue a SELECT * in a "normal" program. Only ask for the columns you are actually going to need. Because, someday, someone, is going to change the schema on the table and your program is (im)pure porcine excrement at that point. With no errors returned to it. IMO, it is an unshakable rule to ONLY and ALWAYS specify the variable names. The only exception is if your program actually examines the schema of the table before doing a SELECT and dynamically constructs it. 

--

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

--

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown