Re: all the table values equal - Mailing list pgsql-sql
| From | Oliveiros d'Azevedo Cristina |
|---|---|
| Subject | Re: all the table values equal |
| Date | |
| Msg-id | CE8B66AA3B894709AABD8426EBFA31C5@marktestcr.marktest.pt Whole thread Raw |
| In response to | all the table values equal (Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>) |
| List | pgsql-sql |
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
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