Thread: all the table values equal
Hi list, this is a Friday afternoon question... :) I have a table like this: 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 I'm trying to create a query that say me which id_user, inside a interval of time, has all the values -1 The part if the interval it's not a problem, but I'm fighting with the aggregate... Someone? Thanks, Michele
Howdy , Michele, Give this a try SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug here beginning of interval AND B -- and end here GROUP BY id_user HAVING COUNT(*) = -SUM(value) Then tell me if it gives you what you want Best, Oliveiros Cristina ----- Original Message ----- From: "Michele Petrazzo - Unipex" <michele.petrazzo@unipex.it> To: <pgsql-sql@postgresql.org> Sent: Friday, September 17, 2010 3:50 PM Subject: [SQL] all the table values equal > Hi list, > this is a Friday afternoon question... :) > I have a table like this: > > 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 > > I'm trying to create a query that say me which id_user, inside a > interval of time, has all the values -1 > > The part if the interval it's not a problem, but I'm fighting with the > aggregate... > Someone? > > Thanks, > Michele > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Oliveiros d'Azevedo Cristina ha scritto: > Howdy , Michele, > > Give this a try > > SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug > here beginning of interval AND B -- and end here GROUP BY id_user > HAVING COUNT(*) = -SUM(value) > > Then tell me if it gives you what you want Thanks, it works, but... it's really a trickle that exploits the value -1 if I understand how its work. If there is another value where look for? Example 13? Thanks
Hello again, Michele, I haven't open my mailbox during weekend so I couldn't follow up your question. 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? Best, Oliver ----- Original Message ----- From: "Michele Petrazzo - Unipex" <michele.petrazzo@unipex.it> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> Cc: <pgsql-sql@postgresql.org> Sent: Friday, September 17, 2010 4:45 PM Subject: Re: [SQL] all the table values equal > Oliveiros d'Azevedo Cristina ha scritto: >> Howdy , Michele, >> >> Give this a try >> >> SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug >> here beginning of interval AND B -- and end here GROUP BY id_user >> HAVING COUNT(*) = -SUM(value) >> >> Then tell me if it gives you what you want > > > Thanks, > it works, but... it's really a trickle that exploits the value -1 if I > understand how its work. > If there is another value where look for? Example 13? > > Thanks > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Oliveiros d'Azevedo Cristina ha scritto: > Hello again, Michele, > Ciao, > I haven't open my mailbox during weekend so I couldn't follow up your > question. > No problem! > 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 Thanks, Michele > Best, Oliver > > ----- Original Message ----- From: "Michele Petrazzo - Unipex" > <michele.petrazzo@unipex.it> To: "Oliveiros d'Azevedo Cristina" > <oliveiros.cristina@marktest.pt> Cc: <pgsql-sql@postgresql.org> Sent: > Friday, September 17, 2010 4:45 PM Subject: Re: [SQL] all the table > values equal > > >> Oliveiros d'Azevedo Cristina ha scritto: >>> Howdy , Michele, >>> >>> Give this a try >>> >>> SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- >>> plug here beginning of interval AND B -- and end here GROUP BY >>> id_user HAVING COUNT(*) = -SUM(value) >>> >>> Then tell me if it gives you what you want >> >> >> Thanks, it works, but... it's really a trickle that exploits the >> value -1 if I understand how its work. If there is another value >> where look for? Example 13? >> >> Thanks >> >> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To >> make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > >
<font face="Verdana">I'm not sure if I understood you right, but it sounds similar to a case which I faced recently. Whynot to use an inverse approach? In other words: trying to find those registries which hasn't got at least one value whichdiffers from which you want to look for. How? Using the EXISTS function with some kind of subquery I guess.<br /><br/><br /></font><br /> On 21/09/10 10:11, Michele Petrazzo - Unipex wrote: <blockquote cite="mid:4C9868AB.6050200@unipex.it"type="cite">Oliveiros d'Azevedo Cristina ha scritto: <br /><blockquote type="cite">Helloagain, Michele, <br /><br /></blockquote><br /> Ciao, <br /><br /><blockquote type="cite">I haven't openmy mailbox during weekend so I couldn't follow up your <br /> question. <br /><br /></blockquote><br /> No problem! <br/><br /><blockquote type="cite">It would help if you explain a little better the background of the <br /> problem you'retrying to solve. <br /><br /> You want to find all the user IDs which have the same value on a <br /> given time interval?<br /><br /> Is my understanding correct? <br /><br /></blockquote><br /> Yes. <br /><br /> Re-reading my post Isaw that I could explain better! <br /><br /> id_user | value | datetime <br /> 1 | 1 | xxx <br /> 1 | 2 | xxx+1 <br />1 | -1 | xxx+2 <br /> 2 | -1 | xxx <br /> 2 | -1 | xxx+1 <br /> 3 | 4 | xxx <br /> 3 | 10 | xxx+1 <br /> 3 | 4 | xxx+2<br /> 4 | 3 | xxx <br /> 4 | 3 | xxx+1 <br /><br /> So, the new question: how I can find which id_user has _all_ the"value" <br /> that I'm looking for? Say -1 as 3 and I want a id_user=2 for the first <br /> and for the latter id_user=4<br /><br /><br /> Thanks, <br /> Michele <br /><br /><blockquote type="cite">Best, Oliver <br /><br /> ----- OriginalMessage ----- From: "Michele Petrazzo - Unipex" <br /><a class="moz-txt-link-rfc2396E" href="mailto:michele.petrazzo@unipex.it"><michele.petrazzo@unipex.it></a>To: "Oliveiros d'Azevedo Cristina" <br /><aclass="moz-txt-link-rfc2396E" href="mailto:oliveiros.cristina@marktest.pt"><oliveiros.cristina@marktest.pt></a>Cc: <a class="moz-txt-link-rfc2396E"href="mailto:pgsql-sql@postgresql.org"><pgsql-sql@postgresql.org></a> Sent: <br /> Friday,September 17, 2010 4:45 PM Subject: Re: [SQL] all the table <br /> values equal <br /><br /><br /><blockquote type="cite">Oliveirosd'Azevedo Cristina ha scritto: <br /><blockquote type="cite">Howdy , Michele, <br /><br /> Give thisa try <br /><br /> SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- <br /> plug here beginning of intervalAND B -- and end here GROUP BY <br /> id_user HAVING COUNT(*) = -SUM(value) <br /><br /> Then tell me if it givesyou what you want <br /></blockquote><br /><br /> Thanks, it works, but... it's really a trickle that exploits the <br/> value -1 if I understand how its work. If there is another value <br /> where look for? Example 13? <br /><br /> Thanks<br /><br /> -- Sent via pgsql-sql mailing list (<a class="moz-txt-link-abbreviated" href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)To <br /> make changes to your subscription: <br /><aclass="moz-txt-link-freetext" href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></blockquote><br/><br /></blockquote><br /><br /><br /></blockquote>
>> 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
Oliveiros d'Azevedo Cristina ha scritto: > > OK, So, analysing your new question, the time interval is still > important? Or no longer? It's important but simple to translate into sql, so I remove it from my question. > So writting your query in english : "I want all users that don't change > "value" and their value is equal to x ?" > Yes. All the users that have only one value into the "value" column and that value is NN. > 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 > A right result for me, based by my data, will be: // choosing -1 like value id_user 2 // choosing 3 like value id_user 4 and if I add to the table: id_user | value | datetime 5 | -1 | xxx 5 | -1 | xxx +1 // choosing -1 like value id_user 2 5 Thanks, Michele
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
> > Yes. All the users that have only one value into the "value" column and > that value is NN. Understood. Try the query from the previous mail, adding the clause WHERE "value" = NN as I did explain. > >> 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 >> > > > A right result for me, based by my data, will be: > > // choosing -1 like value > id_user > 2 > > // choosing 3 like value > id_user > 4 > > and if I add to the table: > id_user | value | datetime > 5 | -1 | xxx > 5 | -1 | xxx +1 > > > // choosing -1 like value > id_user > 2 > 5 Understood. From the query I sent you just eliminate the "value" column from the select list, as you are not interested in it. > Thanks, > Michele Best, Oliver