From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: all the table values equal
Date: Tue, 21 Sep 2010 15:46:49 +0100
Message-ID: <CE8B66AA3B894709AABD8426EBFA31C5@marktestcr.marktest.pt> (raw)
References: <4C93803A.4080706@unipex.it>
<438B32316EF6496D9641819BA16447A2@marktestcr.marktest.pt>
<4C938D09.3050105@unipex.it>
<81C545E3469E4DF58720C282A469E78A@marktestcr.marktest.pt>
<4C9868AB.6050200@unipex.it>
<5E43A989C2FA4F60B146DAC454BAA21D@marktestcr.marktest.pt>
Howdy, Michele.
Any news?
Any progress/problems?
If you want to find out all the id_users who don't change value in a given time interval
you can use something like this.
SELECT "id_user","value"
FROM (
SELECT "id_user", COUNT(*) as total
FROM t_yourtable
WHERE "datetime" BETWEEN '2010-9-1' -- Interval beginning
AND '2010-9-11' -- interval end
GROUP BY "id_user"
) as foo
NATURAL JOIN
(
SELECT "id_user","value",COUNT(*) as total
FROM t_yourtable
WHERE "datetime" BETWEEN '2010-9-1' -- again, plug interval beginning here
AND '2010-9-11' -- and interval end here
GROUP BY "id_user","value"
) as bar
This will give you the users who didn't change value on the time interval.
Finally, if you want to
look for a specific value just add the clause "WHERE "value" = x -- x=the specific value you want to look for
to the query above. It'll return just the id_user(s) that have just the "value" x.
Please try it out and feel free to get back to me if it didn't work or if it didn't solve your problem
Best,
Oliver
----- Original Message -----
From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
To: "Michele Petrazzo - Unipex" <michele.petrazzo@unipex.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, September 21, 2010 11:25 AM
Subject: Re: [SQL] all the table values equal
>
>>> It would help if you explain a little better the background of the
>>> problem you're trying to solve.
>>>
>>> You want to find all the user IDs which have the same value on a
>>> given time interval?
>>>
>>> Is my understanding correct?
>>>
>>
>> Yes.
>>
>> Re-reading my post I saw that I could explain better!
>>
>> id_user | value | datetime
>> 1 | 1 | xxx
>> 1 | 2 | xxx+1
>> 1 | -1 | xxx+2
>> 2 | -1 | xxx
>> 2 | -1 | xxx+1
>> 3 | 4 | xxx
>> 3 | 10 | xxx+1
>> 3 | 4 | xxx+2
>> 4 | 3 | xxx
>> 4 | 3 | xxx+1
>>
>> So, the new question: how I can find which id_user has _all_ the "value"
>> that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first
>> and for the latter id_user=4
>>
>
> OK, So, analysing your new question, the time interval is still important?
> Or no longer?
> So writting your query in english : "I want all users that don't change
> "value" and their value is equal to x ?"
>
> Or will you need to find all the users that don't change value with just one
> query? And the query would return something like
>
> id_user | value
> 2 | -1
> 4| 3
>
> ?
> Please advice me on this.
>
> I am just trying to avoid misunderstanding your requirements and ending up
> with a query that doesn't really do what you need.
>
> Best,
> Oliver
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, michele.petrazzo@unipex.it
Subject: Re: all the table values equal
In-Reply-To: <CE8B66AA3B894709AABD8426EBFA31C5@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox