[SQL] Get the max viewd product_id for user_id

[Mario Splivalo]

I have a log-table where I record when some user_id has viewed some 
product_id:


CREATE TABLE viewlog (
user_id integer,
product_id integer,
view_timestamp timestamp with time zone
)

Now, I would like to get result that gives me, for each user_id, 
product_id of the product he/she viewed the most time, with the number 
of views.


The 'issue' is I need this running on postgres 8.0.

I went this way, but for a large number of user_id's, it's quite slow:

CREATE VIEW v_views AS
SELECT user_id, product_id, count(*) as views
FROM viewlog
GROUP BY user_id, product_id

SELECT
DISTINCT user_id,
	(SELECT product_id FROM v_views inn WHERE inn.user_id = out.user_id 
ORDER BY views DESC LIMIT 1) as product_id,
	(SELECT views FROM v_views inn WHERE inn.user_id = out.user_id ORDER BY 
views DESC LIMIT 1) as views

FROM
v_views out


Mario

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Get the max viewd product_id for user_id

[Jayadevan M]

Hello,

> I went this way, but for a large number of user_id's, it's quite slow:
> 
> CREATE VIEW v_views AS
>SELECT user_id, product_id, count(*) as views
>FROM viewlog
>GROUP BY user_id, product_id
> 
> SELECT
>DISTINCT user_id,
>(SELECT product_id FROM v_views inn WHERE inn.user_id = out.user_id 
> ORDER BY views DESC LIMIT 1) as product_id,
>(SELECT views FROM v_views inn WHERE inn.user_id = out.user_id ORDER 
BY 
> views DESC LIMIT 1) as views
> FROM
>v_views out
> 
Does this work faster?
select x.user_id,y.product_id,x.count from
(select user_id, max(count ) as count from (select user_id,product_id, 
count(*) as count from viewlog group by user_id,product_id) as x group by 
user_id
) as x inner join 
(select user_id,product_id, count(*) as count1 from viewlog group by 
user_id,product_id ) as y
on x.user_id=y.user_id and x.count=y.count1

Regards,
Jayadevan






DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."






-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] COPY with FORMAT in Postgresql 9.x

[Tom Lane]

Humair Mohammed  writes:
> Postgres 9.x provies the COPY command with new syntax with a new option 
> called FORMAT
> http://www.postgresql.org/docs/current/static/sql-copy.html
> However when I run the following command:
> COPY dQstn FROM 'c:/bcp/postgres/dQstn.csv' WITH FORMAT csv;
> I get the following error message:
> ERROR:  syntax error at or near "FORMAT"
> If I use the 8.x syntax without the FORMAT option it works fine.  
>   

This is expected: you have to use the new syntax (with parens) for
any of the new options.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Union Question

[Shaun McCloud]

Hello,

I need to union three PostgreSQL tables and this won't be a problem but the 
tables are on different servers.  Basically, I have an administrative server 
that needs the tables viewable in a web administrator and three query servers 
that log the needed data locally.  Is there a way I can do this without using 
Slony-I to replicate the data to the administrative server?

Shaun McCloud - Software Testing Analyst
GeoComm Inc.
601 W. Saint Germain St., Saint Cloud, MN 56301
Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666
click here to visit www.geo-comm.com
Microsoft Certified Desktop Support Technician (MCDST)

Do or do not, there is no try.
  -Yoda

Re: [SQL] Union Question

[Shaun McCloud]

I'm trying that, but I am getting an error that says "ERROR:  function 
dblink_connect(unknown, unknown) does not exist"

Shaun McCloud - Software Testing Analyst
GeoComm Inc.
601 W. Saint Germain St., Saint Cloud, MN 56301
Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666
click here to visit www.geo-comm.com
Microsoft Certified Desktop Support Technician (MCDST)

Do or do not, there is no try.
  -Yoda


From: Plugge, Joe R. [mailto:jrplu...@west.com]
Sent: Friday, December 03, 2010 10:58
To: Shaun McCloud; pgsql-sql@postgresql.org
Subject: RE: Union Question

You may want to try dblink.

http://www.postgresql.org/docs/current/static/dblink.html


From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Shaun McCloud
Sent: Friday, December 03, 2010 10:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Union Question

Hello,

I need to union three PostgreSQL tables and this won't be a problem but the 
tables are on different servers.  Basically, I have an administrative server 
that needs the tables viewable in a web administrator and three query servers 
that log the needed data locally.  Is there a way I can do this without using 
Slony-I to replicate the data to the administrative server?

Shaun McCloud - Software Testing Analyst
GeoComm Inc.
601 W. Saint Germain St., Saint Cloud, MN 56301
Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666
click here to visit www.geo-comm.com
Microsoft Certified Desktop Support Technician (MCDST)

Do or do not, there is no try.
  -Yoda

Re: [SQL] Union Question

[Plugge, Joe R.]

You may want to try dblink.

http://www.postgresql.org/docs/current/static/dblink.html


From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Shaun McCloud
Sent: Friday, December 03, 2010 10:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Union Question

Hello,

I need to union three PostgreSQL tables and this won't be a problem but the 
tables are on different servers.  Basically, I have an administrative server 
that needs the tables viewable in a web administrator and three query servers 
that log the needed data locally.  Is there a way I can do this without using 
Slony-I to replicate the data to the administrative server?

Shaun McCloud - Software Testing Analyst
GeoComm Inc.
601 W. Saint Germain St., Saint Cloud, MN 56301
Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666
click here to visit www.geo-comm.com
Microsoft Certified Desktop Support Technician (MCDST)

Do or do not, there is no try.
  -Yoda

Re: [SQL] Union Question

[Christophe Pettus]

On Dec 3, 2010, at 9:04 AM, Shaun McCloud wrote:

> I’m trying that, but I am getting an error that says “ERROR:  function 
> dblink_connect(unknown, unknown) does not exist”

dblink is a contrib module, and needs to be installed before use:

http://www.postgresql.org/docs/9.0/interactive/dblink.html

--
-- Christophe Pettus
   x...@thebuild.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Union Question

[Shaun McCloud]

That would be nice to see in the documentation for dblink

Shaun McCloud - Software Testing Analyst
GeoComm Inc.
601 W. Saint Germain St., Saint Cloud, MN 56301
Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666
click here to visit www.geo-comm.com
Microsoft Certified Desktop Support Technician (MCDST)

Do or do not, there is no try.
  -Yoda



-Original Message-
From: Christophe Pettus [mailto:x...@thebuild.com] 
Sent: Friday, December 03, 2010 11:08
To: Shaun McCloud
Cc: Plugge, Joe R.; pgsql-sql@postgresql.org
Subject: Re: [SQL] Union Question


On Dec 3, 2010, at 9:04 AM, Shaun McCloud wrote:

> I'm trying that, but I am getting an error that says "ERROR:  function 
> dblink_connect(unknown, unknown) does not exist"

dblink is a contrib module, and needs to be installed before use:

http://www.postgresql.org/docs/9.0/interactive/dblink.html

--
-- Christophe Pettus
   x...@thebuild.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Union Question

[Christophe Pettus]

On Dec 3, 2010, at 9:08 AM, Shaun McCloud wrote:

> That would be nice to see in the documentation for dblink

It's true of all contrib modules; that's mentioned at the start of the contrib 
section:

http://www.postgresql.org/docs/9.0/interactive/contrib.html
--
-- Christophe Pettus
   x...@thebuild.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Union Question

[Shaun McCloud]

Ah, ok.  My bad for not reading good enough.

Shaun McCloud - Software Testing Analyst
GeoComm Inc.
601 W. Saint Germain St., Saint Cloud, MN 56301
Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666
click here to visit www.geo-comm.com
Microsoft Certified Desktop Support Technician (MCDST)

Do or do not, there is no try.
  -Yoda



-Original Message-
From: Christophe Pettus [mailto:x...@thebuild.com] 
Sent: Friday, December 03, 2010 11:27
To: Shaun McCloud
Cc: Plugge, Joe R.; pgsql-sql@postgresql.org
Subject: Re: [SQL] Union Question


On Dec 3, 2010, at 9:08 AM, Shaun McCloud wrote:

> That would be nice to see in the documentation for dblink

It's true of all contrib modules; that's mentioned at the start of the contrib 
section:

http://www.postgresql.org/docs/9.0/interactive/contrib.html
--
-- Christophe Pettus
   x...@thebuild.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] A note on how to generate a list of months within some period of time.

[Michał Roszka]

Hello,

I am referring to an old message and it's follow-ups.  Today I went
through a similar problem and solved it in a different way.  I would
like to share my approach here to help others who might be searching
for it.  I think it is very simple and flexible.

The old message is here:

http://archives.postgresql.org/pgsql-sql/2010-03/msg00084.php

Esentially, I need to generate an ordered list of months, like:

2010-12
2010-11
2010-10
...
2008-04
2008-03
2008-02

First, let's specify the limits: the current date (max_date)
and some date in the past (min_date).  Additionally let's have them
rounded down to the first day of the month.

max_date = date_trunc('month', current_date); -- 2010-12-01
min_date = '2008-02-01'::date; -- 2008-02-01

You might need the min_date to be calculated based on the actual data:

SELECT INTO min_date date_trunc('month', mytable.created)
FROM mytable ORDER BY mytable.created ASC LIMIT 1;

mytable.created is a column in mytable of type date (or similar) and
here we select the lowest value.  Replace ASC with DESC to select the
highest one.

Let's generate a descending list:

LOOP
RAISE NOTICE '%', max_date;
max_date = max_date - '1 month'::interval;
EXIT WHEN max_date < min_date;
END LOOP;

Replace the RAISE NOTICE statement with one, that fits your needs best.
Note, that the EXIT WHEN statement solves the case when the initial
value of max_date is already lower than the one of min_date.

If you need an ascending list, use min_date instead of max_date and
modify it by addition instead of subtraction.  Leave the EXIT WHEN
statement unchanged:

LOOP
RAISE NOTICE '%', min_date;
min_date = min_date + '1 month'::interval;
EXIT WHEN max_date < min_date;
END LOOP;

You get the idea. ;)

Cheers,

-Mike

-- 
Michał Roszka
m...@if-then-else.pl

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Union Question

[Jasen Betts]

On 2010-12-03, Shaun McCloud  wrote:
> --_000_7742DD496427B743BC8B7BBF6D380BA0A2F114EXCHANGE10geocomm_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> Hello,
>
> I need to union three PostgreSQL tables and this won't be a problem but the=
>  tables are on different servers.  Basically, I have an administrative serv=
> er that needs the tables viewable in a web administrator and three query se=
> rvers that log the needed data locally.  Is there a way I can do this witho=
> ut using Slony-I to replicate the data to the administrative server?

modify the web adminsitrator to connect to the three servers and do the
union itself.

-- 
⚂⚃ 100% natural

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql