Re: [SQL] Bucketing Row Data in columns
Hello, Sandeep, I am not sure if this is what you want. I came up with this query SELECT * FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE create_timestamp = '2009-1-1' GROUP BY user_id) a NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE create_timestamp = '2009-1-2' GROUP BY user_id) b NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day3 FROM t1 WHERE create_timestamp = '2009-1-3' GROUP BY user_id) c The solution is not totally correct because it returns NULL in the places you return 0. It seems the SUM() returns NULL when it gets an all NULL column... Is it problematic for your application ? Also, i 'm not sure if I fully understand your last sentence lets assume the buckets are fixed i.e 3 only. but I wish to get them unlimited i.e day 1 to day 20. You say that the buckets are fixed at 3. So, you mean the table output will always have 4 columns? 3 days plus one for user_id ? If you want 20 buckets it must be a different query... Could you please clarify what you mean when you say that you want to get a bucket unlimited ? Best, Oliveiros - Original Message - From: Sandeep To: pgsql-sql@postgresql.org Sent: Wednesday, June 24, 2009 5:39 PM Subject: [SQL] Bucketing Row Data in columns Hi all, I need help on creating a sql, not a problem even if its pl/sql I have orders table schema is as follow orders(order_id,user_id, create_timestamp, amount) and I want to generate a report like for the past 3 days bucketing purchases i.e SUM(amount) every day in columns i.e result will be having these columns. (user_id, amount_day1, amount_day2, amount_day3) ex: am leaving order_id assume they are auto incrementing and unique, date format dd/mm/ (user_id, create_timestamp, amount) (user1, 01/01/2009,100) (user1, 01/01/2009,100) (user2, 01/01/2009,100) (user2, 02/01/2009,100) (user2, 02/01/2009,100) (user1, 02/01/2009,100) (user2, 03/01/2009,100) (user2, 03/01/2009,100) (user3, 03/01/2009,100) result (user_id, amount_day1, amount_day2, amount_day3) (user1, 200, 200, 0) (user2, 100, 200, 200) (user3, 0, 0, 100) hope you guys got what I am trying to generate through sql. I could get this data in each row, but I want it in columns. Can anyone help me on this? lets assume the buckets are fixed i.e 3 only. but I wish to get them unlimited i.e day 1 to day 20. Regards Sandeep Bandela
Re: [SQL] Bucketing Row Data in columns
I admit that must be a more elegant and faster solution with pl/psql (or whatever other languages) As I don't know nothing about pl/psql I tried with pure sql (if you don't have a hunting dog, hunt with a cat) But obviously this solution doesn't scale well if you have a giant table with lots of columns - Original Message - From: Rob Sargent robjsarg...@gmail.com To: pgsql-sql@postgresql.org Sent: Thursday, June 25, 2009 4:57 PM Subject: Re: [SQL] Bucketing Row Data in columns I would be suspicious of this sort of solution of turning rows into columns by mean of a series of correlated sub-selects. Once the data set gets large and the number of columns goes over 2 or 3 this will in all likelihood not perform well. I had the pleasure of re-writing a report which was based on count() (similar to sum()) per user_id with the counts going into various columns per user. 18000 users, a dozen columns from table of 2 million rows, report took 1,000,000 seconds (yes almost 12 days) to complete. Re-write runs in 5-10 minutes (now at 10M rows) by getting the counts as rows (user, item, count) into a temp table and making the columns from the temp table (pl/psql) Getting the counts takes half the time, making the flattened report takes half the time. Oliveiros Cristina wrote: Hello, Sandeep, I am not sure if this is what you want. I came up with this query SELECT * FROM (SELECT user_id,SUM(amount) as day1 FROM t1 WHERE create_timestamp = '2009-1-1' GROUP BY user_id) a NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day2 FROM t1 WHERE create_timestamp = '2009-1-2' GROUP BY user_id) b NATURAL FULL JOIN (SELECT user_id,SUM(amount) as day3 FROM t1 WHERE create_timestamp = '2009-1-3' GROUP BY user_id) c The solution is not totally correct because it returns NULL in the places you return 0. It seems the SUM() returns NULL when it gets an all NULL column... Is it problematic for your application ? Also, i 'm not sure if I fully understand your last sentence /lets assume the buckets are fixed i.e 3 only. but I wish to get them unlimited i.e day 1 to day 20./ You say that the buckets are fixed at 3. So, you mean the table output will always have 4 columns? 3 days plus one for user_id ? If you want 20 buckets it must be a different query... Could you please clarify what you mean when you say that you want to get a bucket unlimited ? Best, Oliveiros - Original Message - *From:* Sandeep mailto:gibsos...@gmail.com *To:* pgsql-sql@postgresql.org mailto:pgsql-sql@postgresql.org *Sent:* Wednesday, June 24, 2009 5:39 PM *Subject:* [SQL] Bucketing Row Data in columns Hi all, I need help on creating a sql, not a problem even if its pl/sql I have orders table schema is as follow orders(order_id,user_id, create_timestamp, amount) and I want to generate a report like for the past 3 days bucketing purchases i.e SUM(amount) every day in columns i.e result will be having these columns. (user_id, amount_day1, amount_day2, amount_day3) ex: am leaving order_id assume they are auto incrementing and unique, date format dd/mm/ (user_id, create_timestamp, amount) (user1, 01/01/2009,100) (user1, 01/01/2009,100) (user2, 01/01/2009,100) (user2, 02/01/2009,100) (user2, 02/01/2009,100) (user1, 02/01/2009,100) (user2, 03/01/2009,100) (user2, 03/01/2009,100) (user3, 03/01/2009,100) result (user_id, amount_day1, amount_day2, amount_day3) (user1, 200, 200, 0) (user2, 100, 200, 200) (user3, 0, 0, 100) hope you guys got what I am trying to generate through sql. I could get this data in each row, but I want it in columns. Can anyone help me on this? lets assume the buckets are fixed i.e 3 only. but I wish to get them unlimited i.e day 1 to day 20. Regards Sandeep Bandela -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Composite primary keys
Howdy! When you say that pg accepts this silently instead of complaining what are you referring to exactly? First Insert? Why wouldn't it work after all ? What will happen is that when you try to insert a new record without specifying the id column you'll get an error informing that primary key constraint is being violated. But IMHO the first INSERT is legal SQL Best, Oliveiros - Original Message - From: Harald Fuchs hari.fu...@gmail.com To: pgsql-sql@postgresql.org Sent: Tuesday, June 23, 2009 4:14 PM Subject: [SQL] Composite primary keys I tried to throw some invalid SQL to PostgreSQL and found its reaction confusing: $ psql test psql (8.4beta2) Type help for help. test=# CREATE TABLE t1 ( test(# id serial NOT NULL, test(# name text NOT NULL, test(# PRIMARY KEY (id) test(# ); CREATE TABLE test=# CREATE TABLE t2 ( test(# id int NOT NULL REFERENCES t1, test(# language char(3) NULL, test(# txt text NOT NULL, test(# PRIMARY KEY (id, language) test(# ); CREATE TABLE Here's my first gripe: PostgreSQL accepts this silently instead of complaining. test=# INSERT INTO t1 (id, name) VALUES (1, 'text 1'); INSERT 0 1 test=# INSERT INTO t2 (id, language, txt) VALUES (2, NULL, 'text 1 no language'); ERROR: null value in column language violates not-null constraint And here's my second gripe: although PostgreSQL correctly rejects the INSERT it just has ignored my NULL specification. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Obtaining a limited number of records from a long query
Dear List, Is there any way to force a query to return just a few records? For ex, returning just the first ten rows from a long ORDER BY query ? And then, eventually, obtaining the remaining records at a later time, or in background? Thanks in advance for your help, Best, Oliveiros
Re: [SQL] Obtaining a limited number of records from a long query
Thanks a million, Andy. It's precisely what I needed, Indeed! Best, Oliveiros - Original Message - From: Andy Shellam andy-li...@networkmail.eu To: Oliveiros Cristina oliveiros.crist...@marktest.pt Cc: pgsql-sql@postgresql.org Sent: Monday, May 25, 2009 2:45 PM Subject: Re: [SQL] Obtaining a limited number of records from a long query Hi Oliveiros Certainly! What you're looking for is the LIMIT...OFFSET syntax. Some examples: SELECT ... LIMIT 10 - return the first 10 records only. SELECT ... LIMIT 10 OFFSET 10 - return 10 records, starting with record 11. Manual page: http://www.postgresql.org/docs/8.3/interactive/queries-limit.html Regards, Andy Oliveiros Cristina wrote: Dear List, Is there any way to force a query to return just a few records? For ex, returning just the first ten rows from a long ORDER BY query ? And then, eventually, obtaining the remaining records at a later time, or in background? Thanks in advance for your help, Best, Oliveiros -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Obtaining a limited number of records from a long query
Thank you for pointing it out, Ivan. The query I am trying to restrict output for happens to be an ORDER BY query. Actually I wasn't aware of this detail. I'll leave a mental note for myself that results might be unexpected for not ORDER BY queries Best, Oliveiros - Original Message - From: Ivan Sergio Borgonovo m...@webthatworks.it To: pgsql-sql@postgresql.org Sent: Monday, May 25, 2009 3:13 PM Subject: Re: [SQL] Obtaining a limited number of records from a long query On Mon, 25 May 2009 14:55:54 +0100 Oliveiros Cristina oliveiros.crist...@marktest.pt wrote: Thanks a million, Andy. It's precisely what I needed, Indeed! Beware: unless you specify an ORDER BY (and you expect no other record is inserted between one query and the other... or you just don't care) you may incur in unexpected results. A query without an order by is not granted to return the result in the same order. If you can (same session) you may use cursors. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Need help combining 2 tables together
I guess this is pretty much the same as doing SELECT message.id,topic.topic,message.message FROM topics JOIN messages ON topics.id = message.topic ORDER BY message.ID Ain't I right? Best, Oliveiros - Original Message - From: James Kitambara To: Richard Ekblom Cc: pgsql-sql@postgresql.org Sent: Friday, May 22, 2009 3:47 PM Subject: Re: [SQL] Need help combining 2 tables together Dear Richard Ekblom, I think Mr. Adrian Klaver gave you the solution. Mine is the similar solution SELECT message.id,topic.topic,message.message FROM topics, messages WHERE message.topic=topic.id order by message.id; After executing this query you will get the following: id | topic | message +--+-- 1 | My favorite food | I like lasagna! 2 | My favorite food | Pizza is also a favorite 3 | Are squares better then circles? | I like circles, they : remind me of pizza Best Regards, Muhoji James Kitambara Database Administrator, B.Sc. With Computer Science and Statistics (Hons), National Bureau of Statistics, P.O. Box 796, Tel : +255 22 2122722/3Fax: +255 22 2130852, Mobile : +255 71 3307632, Dar es Salaam, Tanzania. -ORGINAL MESSAGE On Friday 22 May 2009 6:48:43 am Richard Ekblom wrote: Hello I have frequently encountered the need of combining two tables into one. First, please take a look at the following table setups... CREATE TABLE topics ( id SERIAL PRIMARY KEY, topic TEXT NOT NULL ); CREATE TABLE messages ( id SERIAL PRIMARY KEY, topic INTEGER REFERENCES topics(id), message TEXT NOT NULL ); Example of a topics table: IDTOPIC 1 Are squares better then circles? 2 My favorite food Example of a messages table: IDTOPICMESSAGE 1 2I like lasagna! 2 2Pizza is also a favorite 3 1I like circles, they remind me of pizza Notice that the number of topics may differ from the number of messages. Now I want to combine these tables with a single SELECT to get... Combined table: ID TOPIC MESSAGE 1My favorite foodI like lasagna! 2My favorite foodPizza is also a favorite 3Are squares better then circles?I like circles, they remind me of pizza I have seen different examples of this with something called JOIN but they always give me only two rows. How can I do this when the two tables may have different sizes to produce exactly the combined table above???
[SQL] performance question
Hi, All Suppose you have a query like this SELECT * FROM t_sitesresumebydate a JOIN t_triple_association c ON IDSiteResume = IDResume WHERE dtDate BETWEEN '2009-2-1' AND '2009-2-3' AND IDHitsAccount = 378284 With this time interval it completes in less than a second. If I add one day to the condition, i.e., if it becomes BETWEEN '2009-2-1' AND '2009-2-4' the performance is radically different, it jumps to a staggering 424 seconds. and the number of records returned is just 117 (against 79, by the former condition) Frankly, I cannot understand the reason for this, it seems the planner is taking radically diferent plans on the two conditions (they are below). I have an index on all the fields used in the query. Can anyone help me in fixing this, please? Thanks in advance for your kind help Best, Oliveiros Hash Join (cost=46644.50..751271.16 rows=117 width=60) (actual time=15821.110..424120.924 rows=247 loops=1) Hash Cond: (c.IDResume = a.IDSiteResume) - Seq Scan on t_triple_association c (cost=0.00..554934.99 rows=29938099 width=32) (actual time=38.253..392251.754 rows=30101626 loops=1) - Hash (cost=46644.30..46644.30 rows=82 width=28) (actual time=2711.356..2711.356 rows=23 loops=1) - Index Scan using fki_FGK_SITERESUME_ACCOUNT on t_sitesresumebydate a (cost=0.00..46644.30 rows=82 width=28) (actual time=881.146..2711.303 rows=23 loops=1) Index Cond: (IDHitsAccount = 378284) Filter: ((dtDate = '2009-02-01'::date) AND (dtDate = '2009-02-04'::date)) Total runtime: 424121.180 ms Nested Loop (cost=108.43..745296.34 rows=79 width=60) (actual time=44.283..311.942 rows=185 loops=1) - Index Scan using fki_FGK_SITERESUME_ACCOUNT on t_sitesresumebydate a (cost=0.00..46644.30 rows=55 width=28) (actual time=5.825..23.828 rows=17 loops=1) Index Cond: (IDHitsAccount = 378284) Filter: ((dtDate = '2009-02-01'::date) AND (dtDate = '2009-02-03'::date)) - Bitmap Heap Scan on t_triple_association c (cost=108.43..12658.83 rows=3515 width=32) (actual time=16.902..16.910 rows=11 loops=17) Recheck Cond: (a.IDSiteResume = c.IDResume) - Bitmap Index Scan on index (cost=0.00..108.43 rows=3515 width=0) (actual time=14.466..14.466 rows=11 loops=17) Index Cond: (a.IDSiteResume = c.IDResume) Total runtime: 312.192 ms
[SQL] Re: [SQL] Relatorio da composiçao de FKs e PKs
Escreve a tua dúvida em inglês, por favor, isto é uma lista de mailing em ingles. He wants to do a report with the composition of each foreign key and primary key, not just their names. He wants to know where postgres keeps that in catalog. Best, Oliveiros - Original Message - From: paulo matadr To: Sql-listas_post Sent: Thursday, March 19, 2009 5:42 PM Subject: [SQL] Relatorio da composiçao de FKs e PKs Bom tarde mais um vez , minha intensao era fazer um relatorio da composicao de cada fk e pk( nao apenas seu nome),alguem sabe onde o postgres guarda isso no catalogo. Abraço a todos. -- Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 - Celebridades - Música - Esportes
Re: [SQL] uso de dblink en php
Escribe tu duda en ingles, por favor
Saludos,
Oliveiros
- Original Message -
From: Jhonny Velasquez c.
To: pgsql-sql@postgresql.org ; pgsql-...@postgresql.org
Sent: Thursday, March 05, 2009 3:50 PM
Subject: [SQL] uso de dblink en php
hola a todos
estoy programando en php
tengo el siguiente esquema de BD
BDACADEMICA
personas(idpersona, nombres, paterno)
BDSIAP
PersonasDatosActualizables(idpersona, direccion, telefono)
son dos bases de datos, de las cuales mediante una sola consulta quiero unir
los datos de la misma persona, creo que se puede hacer con dblink, no se si hay
otra forma mejor.
$result=pg_query( $linkacademica,SELECT personas.nombres,
personas.paterno, s.direccion, s.telefono, FROM personas INNER JOIN
dblink('dbname=bdsiap port=5432 host=localhost user=usuario
password=clave,'SELECT idpersona, direccion, telefono FROM
personasdatosactualizables') AS s(idpersona character(15), direccion character
varying(50), telefono character varying(25)) ON personas.idpersona =
s.idpersona);
usando db link me da el siguiente error, supongo que es por las comillas, he
intentado con comillas dobles y simples pero nada, tambien he intentado usar
variables.
Warning: pg_query() [function.pg-query]: Query failed: ERROR: error de
sintaxis en o cerca de «FROM» at character 69 in /var/www/finanzas/prueba.php
on line 16
espero su ayuda
saludos
jhonvels
--
check out the rest of the Windows Live™. More than mail–Windows Live™ goes
way beyond your inbox. More than messages
Re: [SQL] Grass Root Protectionism
Just follow Google's motto Don't be evil :-) We are all engineers from the same field, so Let's all be friends Best, Oliveiros - Original Message - From: Boycott Tech Forums To: pgsql-sql@postgresql.org Sent: Sunday, February 08, 2009 6:40 AM Subject: [SQL] Grass Root Protectionism I am a Sr. Software Engineer in USA who (like many others) have been unfairly treated with offshore software engineers who have the audacity to take our jobs, yet ask (mostly) American engineers to help them with their technical challenges via Technical Forums (like this one). One solution is a bit of Grass Root Protectionism by boycotting technical forums. Perhaps if it takes an offshore engineer 2 hours to solve a problem, then the employer would see the real cost implication. I encourage American engineers who spend hundreds of thousands of dollars to develop their skills not to give it away so freely. Spread the word... Boycott Tech Forums
Re: [SQL] I need some magical advice
Andreas, Does your table has any field that can be used as primary key? Any ID field? Best, Oliveiros - Original Message - From: Andreas maps...@gmx.net To: pgsql-sql@postgresql.org Sent: Thursday, January 29, 2009 11:56 AM Subject: [SQL] I need some magical advice Hi, I'd like to update some records in a table. Those have a status_id and among other columns a varchar with a name and a create_date. The status_id is 0 if nothing was done with this record, yet. For some reasons I've got double entries which I now want to flag to -1 so that they can be sorted out without actually deleting them since there are other tables referencing them. From every group that shares the same name all should get status_id set to -1 where status_id = 0. The tricky bit is: How could I provide, that 1 of every group survives, even then when all have status_id = 0? Sometimes 2 of a group are touched so both have to stay. e.g. c_date, status_id, name 2008/01/01, 0, A -- -1 2008/01/02, 1, A -- do nothing 2008/01/03, 0, A -- -1 2008/01/01, 0, B -- do nothing (single entry) 2008/01/01, 0, C -- do nothing (oldest 0 survives) 2008/01/02, 0, C -- -1 2008/01/01, 1, D -- do nothing 2008/01/02, 1, D -- do nothing -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] I need some magical advice
Andreas, This seems to work at least on the example you provided, but I am not sure if this is what you want. Also, I'm affraid this gets too slow if your table is very extense, due to the number of JOINS It is possible that there is a more direct way to solve your problem, but at least in plain SQL I couldn't find none :-( Best, Oliveiros UPDATE t_your_table SET status_id = -1 WHERE id IN( SELECT id FROM t_your_table a JOIN ( SELECT c.name,MAX(c.status_id) as estado,MIN(d.oldest) as oldest FROM t_your_table c JOIN( SELECT name,MIN(c_date) as oldest FROM t_your_table WHERE (status_id = 0) GROUP BY name HAVING (COUNT(*) 1)) d ON d.name = c.name GROUP BY (c.name) ) b ON a.name = b.name AND (a.status_id b.estado OR b.oldest a.c_date) WHERE a.status_id = 0 ) - Original Message - From: Andreas maps...@gmx.net To: PostgresSQL list pgsql-sql@postgresql.org Cc: Oliveiros Cristina oliveiros.crist...@marktest.pt Sent: Thursday, January 29, 2009 2:17 PM Subject: Re: [SQL] I need some magical advice Hi, yes, there is a serial as primary key. Lets call it id. Therfore one could use this to find the oldest record. Regards Andreas Oliveiros Cristina schrieb: Andreas, Does your table has any field that can be used as primary key? Any ID field? Best, Oliveiros - Original Message - From: Andreas maps...@gmx.net To: pgsql-sql@postgresql.org Sent: Thursday, January 29, 2009 11:56 AM Subject: [SQL] I need some magical advice Hi, I'd like to update some records in a table. Those have a status_id and among other columns a varchar with a name and a create_date. The status_id is 0 if nothing was done with this record, yet. For some reasons I've got double entries which I now want to flag to -1 so that they can be sorted out without actually deleting them since there are other tables referencing them. From every group that shares the same name all should get status_id set to -1 where status_id = 0. The tricky bit is: How could I provide, that 1 of every group survives, even then when all have status_id = 0? Sometimes 2 of a group are touched so both have to stay. e.g. c_date, status_id, name 2008/01/01, 0, A -- -1 2008/01/02, 1, A -- do nothing 2008/01/03, 0, A -- -1 2008/01/01, 0, B -- do nothing (single entry) 2008/01/01, 0, C -- do nothing (oldest 0 survives) 2008/01/02, 0, C -- -1 2008/01/01, 1, D -- do nothing 2008/01/02, 1, D -- do nothing -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Best way to and from a one-to-many joined table?
*How can I get an AND search (people with Dark Hair AND who are President)?* The two joins didn't work? Or were they too slow ? Best, Oliveiros 2008/12/10 Bryce Nesbitt [EMAIL PROTECTED] Milan Oparnica wrote: This is how I do it, and it runs fast: select p.* from test_people p inner join test_attributes a on p.people_id = a.people_id where a.attribute = @firstAttr or a.attribute = @secondAttr But that does an or search, not and, returning Satan in addition to Obama: select * from test_people p inner join test_attributes a on p.people_id = a.people_id lyell5- where a.attribute = 'Dark Hair' or a.attribute = 'USA President'; +---+-+---+---+ | people_id | person_name | people_id | attribute | +---+-+---+---+ | 8 | Obamba | 8 | USA President | | 8 | Obamba | 8 | Dark Hair | | 8 | Obamba | 8 | Dark Hair | |10 | Satan |10 | Dark Hair | +---+-+---+---+ How can I get an AND search (people with Dark Hair AND who are President)? -- 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] Best way to and from a one-to-many joined table?
Hello, Bryce.
It wasn't supposed to output duplicates.
I have assumed that on the test_attributes u didn't have duplicate records,
i.e.,
you didn't have the same pair (people_id, attribute) more than once... But
it seems you do...
And Hence the duplicate row for Obama .
Why is that?
One person can have exactly the same attribute twice?? :-)
On the execution speed, I do declare that query optimization is an area
I know very little about (just to avoid saying that i know nothing :p ) ,
maybe someone
with more knowledge than me can help you better, but from my
own experience, not just with postgres, but also with other sgbd ,
I can tell that subqueries of the kind WHERE x in (SELECT ... )
have the tendency to be slow, that's why I tried to provide you
a solution with the JOINs
Best,
Oliveiros
2008/12/5 Bryce Nesbitt [EMAIL PROTECTED]
It works (with a DISTINCT clause added because of the duplicated row for
Obama). It has a nice clean looking explain plan. It has the slowest
execution time on this sample table (though that might not mean anything).
SELECT
DISTINCT
person_name
FROM test_people p
JOIN test_attributes a
ON ((a.people_id = p.people_id) AND (a.attribute = 'Dark Hair'))
JOIN test_attributes b
ON ((b.people_id = p.people_id) AND (b.attribute = 'USA President'));
Here's the full test table
$ pg_dump --table=test_people --table=test_attributes -p 5433 -i
CREATE TABLE test_attributes (
people_id integer,
attribute text
);
COPY test_attributes (people_id, attribute) FROM stdin;
10The Devil
9Imaginary
8Dark Hair
8Dark Hair
8USA President
10Dark Hair
\.
CREATE TABLE test_people (
people_id integer DEFAULT nextval('test_sequence'::regclass) NOT NULL,
person_name text
);
COPY test_people (people_id, person_name) FROM stdin;
8Obamba
9Santa
10Satan
\.
Oliveiros Cristina wrote:
Howdy, Bryce
Could you please try this out and tell me if it gave what you want.
Best,
Oliveiros
SELECT person_name
FROM test_people p
JOIN test_attributes a
ON ((a.people_id = p.people_id) AND (a.attribute = @firstAttr))
JOIN test_attributes b
ON ((b.people_id = p.people_id) AND (b.attribute = @secondAttr));
Re: [SQL] function - string ends with
If I understand what you need, I guess this clause does work. WHERE string LIKE '%substring' Best, Oliveiros - Original Message - From: Kevin Duffy To: pgsql-sql@postgresql.org Sent: Friday, November 21, 2008 4:30 PM Subject: [SQL] function - string ends with Hello All: Is anyone aware of a function in PgSQL that will check if a string ends with a specific string? I.e. rposition(substring in string ) returns int Starts searching right to left within string Thanks for your attention to this matter. Kevin Duffy
Re: [SQL] Subsorting GROUP BY data
If it is to Group the items by cat field then select the ID where the num is the highest in group, You could maybe try SELECT a.ID, b.Cat,b.Num FROM my_table a JOIN ( SELECT cat, MAX(num) as maximo FROM my_table GROUP_BY cat) b ON a.Cat = b.Cat AND a.Num = b.maximo; It 'll probably give what you need (with minor fixes...) Best, Oliveiros - Original Message - From: Johnson, Michael L. [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Sent: Monday, November 10, 2008 2:56 PM Subject: [SQL] Subsorting GROUP BY data Given the following table: ID | Cat | Num |---|-- Z | A | 0 Y | A | 1 X | A | 2 W | B | 0 V | B | 1 U | B | 2 T | C | 0 S | C | 1 R | C | 2 I want to do this: Group the items by the cat field. Then select the ID where the num is the highest in the group; so it should return something like: Cat | ID | Num -|--|-- A | X | 2 B | U | 2 C | R | 2 Using SQL like this, I can get the category and the highest # in the category: SELECT cat, MAX(num) FROM my_table GROUP_BY cat; But if I add the id column, of course it doesn't work, since it's not in an aggregate function or in the GROUP_BY clause. So I found a post at http://archives.postgresql.org/pgsql-hackers/2006-03/msg01324.php which describes how to add a FIRST and LAST aggregate function to PGSQL. However, first and last don't seem to help unless you are able to subsort the grouping by the # (ie, group by cat, then subsort on num, and select the last one of the group). I would think something like the following would work, except that PGSQL does not like the SQL generated (it basically says I can't have a GROUP_BY after an ORDER_BY). And if I move the ORDER_BY to the end, that just orders the returned groupings, so that doesn't help me either. SELECT cat, LAST(id), LAST(num) FROM my_table ORDER_BY num GROUP_BY cat; So does anyone know how to sort *within* a grouping so that FIRST and LAST return meaningful results? Thanks in advance, Mike Johnson -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Subqueries
alias v not visible in sub-query? - Original Message - From: Pascal Tufenkji To: pgsql-sql@postgresql.org Sent: Thursday, October 30, 2008 12:17 PM Subject: [SQL] Subqueries Hello, I don't understand the following error. Can anyone help me plz Thx Pascal select * from sip_vacations_v v left join ( select pe.emp_id,mg.mat_id,mg.groupe,count(p.id) * 1.5 as nb_heures from mat_grp_v mg inner join planification_v p on p.mat_grp_id = mg.id inner join planification_ens_v pe on pe.planification_id = p.id where mg.annee_univ = v.annee and mg.semestre = v.sem_civ group by pe.emp_id,mg.mat_id,mg.groupe ) p on p.emp_id = v.emp_id and p.mat_id = v.mat_id and p.groupe = v.groupe ERROR: invalid reference to FROM-clause entry for table v LINE 9: where mg.annee_univ = v.annee and mg.semestre = v.sem_civ ^ HINT: There is an entry for table v, but it cannot be referenced from this part of the query.
Re: [SQL] Performing intersection without intersect operator
Howdy, Nacef, Try this, SELECT a.userid FROM orders a JOIN orders b USING (userid) WHERE a.productid = 1 AND b.productid = 2 Best, Oliveiros - Original Message - From: Nacef LABIDI To: pgsql-sql@postgresql.org Sent: Tuesday, October 28, 2008 11:14 AM Subject: [SQL] Performing intersection without intersect operator Hi all, I want to perform an intersection between several select queries but without using the INTERSECT keyword. select userid from orders where productid=1 INTERSECT select userid from orders where productid=2 I want to transform it without the INTERSECT. Thanks to all Nacef
Re: [SQL] grouping/clustering query
Your script is handy, Steve.
Spontaneously, This seems to be an array type problem, something I just have
vague notions about.
I'll take a look at this,
http://www.postgresql.org/docs/8.3/static/arrays.html to see if something
occurs...
Best,
Oliveiros
- Original Message -
From: Steve Midgley [EMAIL PROTECTED]
To: Joe [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org; David Garamond [EMAIL PROTECTED]
Sent: Friday, October 24, 2008 4:04 PM
Subject: Re: [SQL] grouping/clustering query
At 11:28 AM 10/23/2008, Joe wrote:
Steve Midgley wrote:
# (invoiceid, txid)
(A, 1)
(A, 3)
(B, 1)
(B, 2)
(C, 5)
(D, 6)
(D, 7)
(E, 8)
(F, 8)
For journalling, I need to group/cluster this together. Is there a SQL
query that can generate this output:
# (journal: invoiceids, txids)
[A,B] , [1,2,3]
[C], [5]
[D], [6,7]
[E,F], [8]
Hi Dave,
I'm not following the logic here. A has 1,3 and B has 1,2. So why does
the first line print:
[A,B] , [1,2,3]
What's the rule that tells the query to output this way? Is it that all
of B's values are between A's values?
From a purely accounting standpoint, since transaction 1 was applied to
both invoices A and B, you need to group the invoices so that you can
compare total invoiced against total paid.
I tinkered around briefly but didn't come up with a good idea, but I bet
someone on this list can. However, I did create a CREATE script for your
table design which, in my experience, makes it more likely that a real
expert will take on your problem..
Hope this helps,
Steve
DROP TABLE IF EXISTS trans;
CREATE TABLE trans
(
id serial NOT NULL,
inv_id character varying,
tx_id character varying,
CONSTRAINT pk_id PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
insert into trans (inv_id, tx_id) values('A','1');
insert into trans (inv_id, tx_id) values('A','3');
insert into trans (inv_id, tx_id) values('B','1');
insert into trans (inv_id, tx_id) values('B','2');
insert into trans (inv_id, tx_id) values('C','5');
insert into trans (inv_id, tx_id) values('D','6');
insert into trans (inv_id, tx_id) values('D','7');
insert into trans (inv_id, tx_id) values('E','8');
insert into trans (inv_id, tx_id) values('F','8');
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
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] Postgres-sql-php
Howdy, Zied.
The query below outputs the results as you want, but
I suspect you have a more general situation you want to solve.
If you have more than one father, say manuel, you would want something like
this ?
num father child age
1 joe bruce14
lei 10
mike 5
2manuel child135
child233
child3 30
Confirm, please .
Also, do you want the output ordered by age? always?
If so , tell me and we can tweak a little the query to best-fit your needs
Best,
Oliveiros
SELECT a.num,a.father,b.child,b.age
FROM
(
SELECT num,father, MAX(age)as maximo
FROM t1
GROUP BY num,father) a
RIGHT JOIN t1 b
ON b.age = a.maximo
- Original Message -
From: Zied Kharrat
To: pgsql-sql@postgresql.org
Sent: Thursday, October 23, 2008 9:14 AM
Subject: [SQL] Postgres-sql-php
Hi Everybody..
Let's present my problem:
I have a table named t1 and i will insert differents values like this :
insert into t1 (num,father,child,age) values ('1','joe','bruce','14',);
insert into t1 (num,father,child,age) values ('1','joe','lei','10',);
insert into t1 (num,father,child,age) values ('1','joe','mike','5',);
when i use select * from t1 i obtain:
num father child age
1 joe bruce14
1 joe lei 10
1 joe mike 5
i want to have
num father child age
1 joe bruce14
lei 10
mike 5
what can i do as select request to obtain this capture?
Thanks :)
Re: [SQL] Postgres-sql-php
Just add two conditions on the previous query A particularity of this approach is that the non-null record will always appear with the first child in alphabetical order. But, according to what you explain, I guess it is OK, and if it doesn't it is easily changed. :-) Also I've changed the first condition on the right outer join coz it would give trouble if two childs happened to be of the same age. I guess it will never happen two childs with the same name :p Best, Oliveiros SELECT a.num,a.father,b.child,b.age FROM ( SELECT num,father, MIN(child)as primeira FROM t1 GROUP BY num,father) a RIGHT JOIN t1 b ON b.child = a.primeira AND b.father = a.father AND a.num = b.num -- even the biggest failure, even the worst mistake, beats the hell out of never trying... - Meredith Grey 2008/10/23 Zied Kharrat [EMAIL PROTECTED] hi, i don't want any sort.. just like this example *num father child age col5 *1 joe bruce14 8 lei 10 mike 5 2manuel child135 16 child233 child3 30 this is what i want really.. What can be then the sql request without sort with this update.. Thanks :)
Re: [SQL] Postgres-sql-php
I guess you can change a little the query to your needs. The problem is pretty much the same... I've used c3 column in equality, but if this column has repeated values, just choose any column or combination of columns which is unique. Best, Oliveiros SELECT a.c1,a.c2,b.c3,b.c4,a.c5,b.c6 FROM ( SELECT c1,c2,c5, MIN(c3)as primeiraFROM t1 GROUP BY c1,c2,c5 ) a RIGHT JOIN t1 b ON b.c3 = a.primeira AND b.c1 = a.c1 AND a.c2 = b.c2 AND a.c5 = b.c5 2008/10/23 Zied Kharrat [EMAIL PROTECTED] Really, i have this schema: *c1 c2 c3 c4 c5 c6* *v1* *v2* v3 v4 *v5* v6 *v1* *v2* v7 v8 *v5* v9 *v1* *v2* v10v11*v5* v12 how can i do my sql request to obtain this? *c1 c2 c3 c4 c5 c6* *v1* *v2* v3 v4 *v5* v6 ** v7 v8 ** v9 * * v10v11* * v12 Thank u very much :) -- even the biggest failure, even the worst mistake, beats the hell out of never trying... - Meredith Grey
Re: [SQL] Search fields in multiple tables
Howdy, Raju. Could you please provide a more concrete example of what you need? What information would you like your query to return exactly? An ordered set of records? Ordered by rank...? Please give an example of a search operation and intended result Best, Oliveiros - Original Message - From: Raj Mathur [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Sent: Thursday, October 16, 2008 5:01 AM Subject: [SQL] Search fields in multiple tables Hi, I have some information about books spread over multiple tables (title, authors, ISBN, blurb, publisher, etc.) Is there any convenient way in PostgreSQL to allow a user to search these in a single operation? If there is, would there be some way to assign weights to the fields? E.g. a match on title would rate higher than a match on publisher or on blurb. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] sequence number in a result
Howdy, Lance. I had that problem about a year ago, and AFAIK there is no solution, at least not in SQL Standard. What I did was something like SELECT a,b,c,count(y.a) as order FROM t1 x , t1 y WHERE ((x.a y.a) OR (x.a = y.a AND x.ID = y.ID)) -- Use here whatever you have as primary key on your table... GROUP BY x.a,x.b,x.c ; ORDER BY a ; But this trick is just for relatively small tables. When I needed something for bigger tables, I did it programmatically But, maybe PostGreSQL has some proprietary function which I dunno that can do precisely this... HTH a little... Best, Oliveiros (SELECT resumo1.\iPages\,resumo1.\iUniqueVisitors\,resumo1.\IDSite\,resumo1.\IDWeboramaAccount\,resumo1.\Visits\, + COUNT(resumo2.\iPages\) as rank + FROM + m_strSUBQUERY_INTERFACE + resumo1, + + m_strSUBQUERY_INTERFACE + resumo2 + WHERE ((resumo1.\dtDate\ = @diadehoje) + AND (resumo2.\dtDate\ = @diadehoje)) + AND ((resumo1.\iPages\ resumo2.\iPages\ ) + OR (resumo1.\iPages\ = resumo2.\iPages\ + AND resumo1.\IDSiteResume\ = resumo2.\IDSiteResume\)) + // Tinha e tem IDSiteResume GROUP BY resumo1.\iPages\,resumo1.\iUniqueVisitors\, + resumo1.\IDSite\,resumo1.\dtDate\,resumo1.\IDSiteResume\,resumo1.\IDWeboramaAccount\,resumo1.\Visits\ - Original Message - From: Campbell, Lance To: pgsql-sql@postgresql.org Sent: Thursday, October 09, 2008 5:31 PM Subject: [SQL] sequence number in a result Say I have the following SQL statement: SELECT a, b, c FROM t1 ORDER BY a; Is there a function or special system label I can use that would generate a sequence number in the returning result set? Example: SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a; Result: ab c order - Aa bb cc 1 A1 bb cc 2 A2 bb cc 3 Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
Re: [SQL] sequence number in a result
Sorry, Lance By mistake I posted my own things, which I was using to adapt to your situation. :-) Please kindly ignore everything below my signature :p Best, Oliveiros - Original Message - From: Oliveiros Cristina To: Campbell, Lance ; pgsql-sql@postgresql.org Sent: Thursday, October 09, 2008 5:48 PM Subject: Re: [SQL] sequence number in a result Howdy, Lance. I had that problem about a year ago, and AFAIK there is no solution, at least not in SQL Standard. What I did was something like SELECT a,b,c,count(y.a) as order FROM t1 x , t1 y WHERE ((x.a y.a) OR (x.a = y.a AND x.ID = y.ID)) -- Use here whatever you have as primary key on your table... GROUP BY x.a,x.b,x.c ; ORDER BY a ; But this trick is just for relatively small tables. When I needed something for bigger tables, I did it programmatically But, maybe PostGreSQL has some proprietary function which I dunno that can do precisely this... HTH a little... Best, Oliveiros (SELECT resumo1.\iPages\,resumo1.\iUniqueVisitors\,resumo1.\IDSite\,resumo1.\IDWeboramaAccount\,resumo1.\Visits\, + COUNT(resumo2.\iPages\) as rank + FROM + m_strSUBQUERY_INTERFACE + resumo1, + + m_strSUBQUERY_INTERFACE + resumo2 + WHERE ((resumo1.\dtDate\ = @diadehoje) + AND (resumo2.\dtDate\ = @diadehoje)) + AND ((resumo1.\iPages\ resumo2.\iPages\ ) + OR (resumo1.\iPages\ = resumo2.\iPages\ + AND resumo1.\IDSiteResume\ = resumo2.\IDSiteResume\)) + // Tinha e tem IDSiteResume GROUP BY resumo1.\iPages\,resumo1.\iUniqueVisitors\, + resumo1.\IDSite\,resumo1.\dtDate\,resumo1.\IDSiteResume\,resumo1.\IDWeboramaAccount\,resumo1.\Visits\ - Original Message - From: Campbell, Lance To: pgsql-sql@postgresql.org Sent: Thursday, October 09, 2008 5:31 PM Subject: [SQL] sequence number in a result Say I have the following SQL statement: SELECT a, b, c FROM t1 ORDER BY a; Is there a function or special system label I can use that would generate a sequence number in the returning result set? Example: SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a; Result: ab c order - Aa bb cc 1 A1 bb cc 2 A2 bb cc 3 Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
Re: [SQL] For each key, find row with highest value of other field
Howdy, Raju Do you want somethin like this? Not sure if I fully understood your requests... SELECT Key,MAX(Date) FROM t_yourTable WHERE Value LIKE 'foo' GROUP BY Key ; Best, Oliveiros - Original Message - From: Raj Mathur [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Sent: Friday, October 03, 2008 7:55 PM Subject: [SQL] For each key, find row with highest value of other field I have some data of the form: Key | Date | Value A | 2008-05-01 | foo* A | 2008-04-01 | bar A | 2008-03-01 | foo* B | 2008-03-04 | baz B | 2008-02-04 | bar C | 2008-06-03 | foo* C | 2008-04-04 | baz C | 2008-03-04 | bar Is there any way to select only the rows marked with a (*) out of these without doing a join? I.e. I wish to find the row with the highest Date for each Key and use the Value from that. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] optimizing a query over tree-like structure
Hi, Svil I 'd like to first fully understand the background of your problem before figurin out if I can be of any help (or not). You have a tree, of which N is the root, is this correct? Then, what are the next sublevel? F, P and R? If so, why is R linked to a sibling (F) ? And the next one? O and Z? Is O connected to itself? And i am not understanding your concept of shortcuts. Could you please explain ? What kind of tree do you have exactly? Binary? Trenary? The mm_* tables keep relations between nodes, I guess If so , the mm_N2Z one is empty, in this example, right?As there is no edge from N to Z (not direct). But what is the Nazn table? What records does it keep? And what is the ownership table? And the value? Could you tell which columns these tables have, at least the relevant ones for your problem ? Please kindly advice me on these issues. I am not very specialized in optimizing queries, but I see you have a lot of cartesian products on your FROM clause, which, from my own experience, I guess it has tendency to be slow... Best, Oliveiros - Original Message - From: [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Sent: Tuesday, September 30, 2008 9:32 AM Subject: [SQL] optimizing a query over tree-like structure hi. sorry for the vague syntax used below, but the query is huge so i've tried to present it in simple terms. And sorry if i'm doing obviously stupid things, i have lots of years programming behind me but NO sql involved. i have a somewhat tree-like structure of objects that link to each other via many2many associations. it looks like: (N is root) N links to R,P,F R links to F P links to O,F O links to O,F #recursively F links to Z All links to F but the one in O are shortcuts, to avoid looking it up recursively. each of these objects has some associated values (again many2many, ownership). what i want is to get all the values related to a given N and its sublevels, in one query. one variant of what i've invented so far is (~pseudocode, no recursion on O): SELECT ownership.*, value.* FROM Nazn, mm_N2P, mm_P2O, mm_O2O, mm_O2O AS mm_O2O1, mm_N2Z, ownership JOIN value ON ownership.value = value.dbid WHERE ( N.dbid = ownership.N OR N.dbid = mm_N2R.left AND mm_N2R.right = ownership.R OR N.dbid = mm_N2P.left AND ( mm_N2P.right = ownership.P OR mm_N2P.right = mm_P2O.left AND ( mm_P2O.right = ownership.O OR mm_P2O.right = mm_O2O.left AND ( mm_O2O.right = ownership.O OR mm_O2O.right = mm_O2O1.left AND mm_O2O1.right = ownership.O ))) OR Nazn.dbid = mm_N2F.left AND ( mm_N2F.right = ownership.F OR mm_N2Z.right = ownership.Z ) ) AND ownership.value = value.dbid AND N.obj = whatever-filter-by-N this scales very poor. it uses the shortcut to F present in N. for just 200 rows with related associations, it takes 4 seconds to get result. if i use the shortcut to F present in P, it takes 2 seconds - but thats still inacceptable. seems that the number or consequtive ORs on same level is killing it. EXPLAIN gives nested loops all over. What am i doing wrong here? should i expand the A-to-B links of the sort mm_N2P.right = mm_P2O.left into mm_N2P.right = P.dbid and P.dbid == mm_P2O.left ? the query is generated via sqlalchemy and a layer on top, so i can tweak it any way required (and it has many other sub/filterings which i've ommited for brevity - they dont make it better/worse). any pointers of how such queries should be written are appreciated - e.g. what is considered fine, what doable and what is a no-no. thanks ahead ciao svil www.svilendobrev.com dbcook.sf.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Finding sequential records
Can this be what you need? Best, Oliveiros SELECT id FROM dummy a NATURAL JOIN ( SELECT fkey_id,name FROM dummy GROUP BY fkey_id,name HAVING COUNT(*) 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) - MIN(id) + 1) / 2 ) b ORDER BY id; - Original Message - From: Steve Midgley [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Sent: Friday, September 26, 2008 6:39 PM Subject: [SQL] Finding sequential records Hi, I've been kicking this around today and I can't think of a way to solve my problem in pure SQL (i.e. I can only do it with a looping/cursor-type solution and some variables). Given a table with this DDL/data script: drop table if exists dummy; create table dummy ( id integer primary key, name varchar(255), fkey_id integer ) ; insert into dummy (id, name, fkey_id) values (502163,'3Br/3Ba Pool Villa in Westin St. John, USVI- Summer 2008',500100); insert into dummy (id, name, fkey_id) values (502164,'3Br/3Ba Pool Villa in Westin St. John, USVI- Summer 2008',500100); insert into dummy (id, name, fkey_id) values (502169,'Lazy Bear Lodge',105); -- not sequential id to previous insert into dummy (id, name, fkey_id) values (502199,'Lazy Bear Lodge',105); insert into dummy (id, name, fkey_id) values (502170,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500089); insert into dummy (id, name, fkey_id) values (502171,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500089); -- not sequential id nor duplicate fkey_id to previous insert into dummy (id, name, fkey_id) values (502175,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500102); insert into dummy (id, name, fkey_id) values (502213,'Sea Watch',500128); -- not duplicate fkey_id to previous insert into dummy (id, name, fkey_id) values (502214,'Sea Watch',500130); Find all instances where * name is duplicated * fkey_id is the same (for the any set of duplicated name fields) * id is sequential (for any set of duplicated name fields) The system should return 502163 502164 502170 502171 Here's as far as I got: select id from dummy where name in ( select name from dummy group by name having count(name)1 ) order by id I can't figure out how to test for duplicate fkey_id when name is the same, nor to test for sequential id's when name is the same. Having a method for either would be great, and both would be a bonus! It seems like there's a clever way to do this without cursors but I can't figure it out! Thanks for any help! Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Finding sequential records
Howdy, Steve. SELECT id FROM dummy a NATURAL JOIN ( SELECT fkey_id,name FROM dummy GROUP BY fkey_id,name HAVING COUNT(*) 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) - MIN(id) + 1) / 2 ) b ORDER BY id; The GROUP BY clause is to associate records that have the same fkey_id and name The COUNT(*) 1 eliminates the situations when there is just one. Now, about the equality, now i am thinking and maybe it is a bazooka to kill a fly. :) In your table you just have duplicates? Or you may have triplicates? And quadruplicates? And in general n-uplicates? At the time, I thought you might have n-uplicates, so I designed the query to be as general as possible to handle all that cases, from which duplicates are a particular case, but now i am wondering if you don't have more than duplicates. Well, anyway the idea is as follows The sum of a sequence is given by first + last / 2 * n, with n = last - first + 1, OK ? So, if the set of ids is sequencial, its sum must equal that expression. It's basically that. But I am now wondering now that I might have misunderstood what your requests were... If you just have duplicates, then maybe it is cleaner to substitute that clause by something simpler, like MAX(id) - MIN(id) = 1 I dunno if I fully answered your questions, but if I didn't feel free to ask Best, Oliveiros -- We are going to have peace even if we have to fight for it. - General Dwight D. Eisenhower Teremos paz, nem que tenhamos de lutar por ela - General Dwight D. Eisenhower
Re: [SQL] exclusion query
Hi, Louis-David, I guess you already have your problem solved, but just for the sake of curiosity, another way to do it might be to tweak a little your original query, I've written on Capitals the things I've added. Should you need to exclude more than one event you can add the conditions to the commented line (ORed ) Best, Oliveiros select distinct pt.type from person_type pt natural join person_to_event join event e using (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 -- put here the id of the event you wanna exclude join event_type et ON e.id_event_type = et.id_event_type where et.type_fr='théâtre' GROUP BY pt.type_fr HAVING SUM(e2.id_event) IS NULL; - Original Message - From: Louis-David Mitterrand [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Sent: Tuesday, September 23, 2008 9:18 AM Subject: Re: [SQL] exclusion query On Mon, Sep 22, 2008 at 09:39:08AM -0700, Mark Roberts wrote: Taking your second email into account, I came up with: select distinct pt.type_fr from person_to_event pte inner join person_type using (id_person_type) where id_person_type in ( select id_person_type from person_to_event pte inner join event using (id_event) inner join event_type using (id_event_type) where type_fr = 'theatre' ) and id_person_type not in ( select id_person_type from person_to_event where id_event = 219 ) I feel like there's a solution involving group by tugging at the back of my mind, but I can't quite put my finger on it. Sorry if this isn't quite what you're asking for. Hi, That works very nicely (with minor adaptations). I also had that solution-without-a-subselect in the back of my mind but this does the job just fine! Cheers, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question on partitioning
Hello , All. I am not sure if this is the right mailing list to place this question. If it doesn't, please kindly redirect me to the right list. I have a giant table with about 10,6 million records. Queries on it are usually slow, and if I try to do something more elaborate like an INNER JOIN with itself it becomes unnacceptably slow. I am looking for a way to improve performance. One of the columns is of type date. Each day includes about a few tens of thousands records And the older a date is the less likely I am to do queries on it. The objective of the self join is to compare data from two different days, looking for diferences. Ive read that one of the benefits of partitioning is to speed up queries by separating less used records. My question is if partitioning can be a good way to make the queries faster (specially the self joins) or if it isn't worth trying because it doesn't help on my particular situation. Please kindly advice me on this Many thanks in advance for your kind help Best, Oliveiros
Re: [SQL] Join question
I don't understand your count(total) expression... It doesnt work, because apparently you dont have any total column... Apparently, you meant count(color) The problem is that you are grouping by a.org,a.user and on table a u actually dont have any null users... Well, if it is to include null users, a quick and dirty solution I can think of would be to add a dummy null user to every diferent org on table a and then substitute your LEFT OUTER JOIN condition by this one : from a left join b on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null ))) Now, I don' know if null users on table a will violate any constraints you may have (e.g. NOT NULL) ... I know This is not a very elegant solution, but seems to give the results you need Best, Oliveiros - Original Message - From: Daniel Hernandez To: pgsql-sql@postgresql.org ; [EMAIL PROTECTED] Sent: Monday, August 18, 2008 5:30 PM Subject: Re: [SQL] Join question have you tried a right Join? Daniel Hernndez. San Diego, CA. The more you learn, the more you earn. Fax: (808) 442-0427 -Original Message- From: Edward W. Rouse [EMAIL PROTECTED] Date: 08/15/2008 09:48 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = ‘A’ group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] column names with - and (
Maria, The minus, open bracket and close bracket are illegal as identifier names http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Best, Oliveiros - Original Message - From: maria s To: Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org ; Pavel Stehule ; Ivan Sergio Borgonovo Sent: Tuesday, July 29, 2008 5:06 PM Subject: [SQL] column names with - and ( Hi All, When I am fetching the data from a table, I am giving the column names with - and ( and ) like IL-a, IL-a(p30) etc.. select x1 as IL-a, x2 as IL-a(p30) from abc But I am getting ERROR: syntax error at or near - and also t ( , ) Can anyone help me to fix this? Thank you, Maria
Re: [SQL] column names with - and (
You can enclose the names in quotes, then the characters are allowed Sorry to mislead you :p Best, Oliveiros - Original Message - From: Oliveiros Cristina To: maria s ; Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org ; Pavel Stehule ; Ivan Sergio Borgonovo Sent: Tuesday, July 29, 2008 5:27 PM Subject: Re: [SQL] column names with - and ( Maria, The minus, open bracket and close bracket are illegal as identifier names http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Best, Oliveiros - Original Message - From: maria s To: Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org ; Pavel Stehule ; Ivan Sergio Borgonovo Sent: Tuesday, July 29, 2008 5:06 PM Subject: [SQL] column names with - and ( Hi All, When I am fetching the data from a table, I am giving the column names with - and ( and ) like IL-a, IL-a(p30) etc.. select x1 as IL-a, x2 as IL-a(p30) from abc But I am getting ERROR: syntax error at or near - and also t ( , ) Can anyone help me to fix this? Thank you, Maria
Re: [SQL] Accessing array datatype
Maria, You mean your array has a variable size, which you need to know in order to properly construct a query? Dunno much about arrays, but here http://www.postgresql.org/docs/8.3/static/arrays.html is said that array_upper( your_array, 1 ) returns the upper bound of the array... Can this be what you need...? Best, Oliveiros - Original Message - From: maria s To: Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org ; Pavel Stehule ; Ivan Sergio Borgonovo Sent: Monday, July 28, 2008 5:09 PM Subject: [SQL] Accessing array datatype Hi All, I have an array column in a table. How can I fetch the elements separately( ie, select arr[1],arr[2]...arr[n]) as a column, when I don't know how many elements are there in the array? Thanks for your help. Maria
Re: [SQL] Accessing array datatype
Maria,
Could you explain a little more the background?
What kind of output do you actually want?
.Do you need to retrieve all the arrays in that column in just one query?
Or just need to get one with a particular ID?
I learned bout the existence of arrays a few days ago :-)
If I had this problem and with my current knowledge on arrays I would issue
first one query to obtain the dimension and then programmatically I would build
the next query with the exact number of columns (I use C# for client programs).
It would work fine if you are just hunting for a particular record on your
table, but I don't know if that's your goal
Also, Please don't send private replies, always include the mailing list address
because someone with more knowledge than me might be able to quickly help you
;-)
Best,
Oliveiros
- Original Message -
From: maria s
To: Oliveiros Cristina
Sent: Monday, July 28, 2008 6:03 PM
Subject: Re: [SQL] Accessing array datatype
Hi Oliveiros,
Thank you so much for your reply.
I would like to get the elements of an array as columns instead of getting
them as {a,b,..}
May be if I know the array boundary, then I can fetch the elements as I wish.
But I don't know is there any other way to get the data.
Thank you for your reply.
Maria
On Mon, Jul 28, 2008 at 12:56 PM, Oliveiros Cristina [EMAIL PROTECTED]
wrote:
Maria,
You mean your array has a variable size, which you need to know in order to
properly construct a query?
Dunno much about arrays, but here
http://www.postgresql.org/docs/8.3/static/arrays.html is said that array_upper(
your_array, 1 ) returns the upper bound of the array...
Can this be what you need...?
Best,
Oliveiros
- Original Message -
From: maria s
To: Osvaldo Rosario Kussama ; pgsql-sql@postgresql.org ; Pavel Stehule ;
Ivan Sergio Borgonovo
Sent: Monday, July 28, 2008 5:09 PM
Subject: [SQL] Accessing array datatype
Hi All,
I have an array column in a table.
How can I fetch the elements separately( ie, select
arr[1],arr[2]...arr[n]) as a column,
when I don't know how many elements are there in the array?
Thanks for your help.
Maria
Re: [SQL] How to GROUP results BY month
Still another way to do :-) Thanks, Lennin. Best, Oliveiros - Original Message - From: Lennin Caro [EMAIL PROTECTED] To: pgsql-sql@postgresql.org; A. Kretschmer [EMAIL PROTECTED] Sent: Friday, July 18, 2008 3:04 PM Subject: Re: [SQL] How to GROUP results BY month i think this work select id,count from table group by to_char(date,'MM') --- On Wed, 7/16/08, A. Kretschmer [EMAIL PROTECTED] wrote: From: A. Kretschmer [EMAIL PROTECTED] Subject: Re: [SQL] How to GROUP results BY month To: pgsql-sql@postgresql.org Date: Wednesday, July 16, 2008, 5:39 AM am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts folgendes: On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes: Howdy, all, I have a problem. I have a table which one of the fields is of type date. I need to obtain the totals of the other fields in a by-month basis IS there any easy way to do this using the GROUP BY or any other construct? ... group by extract(month from date) Andreas It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queries that span years. Right, but that wasn't the question... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] How to GROUP results BY month
All, Thanks a million for your help and thoughtful considerations. From this thread I learned lots. As my concrete problem just concerns one year, I used the extract function, but I ve mentally wrote down the date_trunc construct as suggested by Herouth Andreas and Scott, thanks for the tips on indexing Again, thanks a lot. Best, Oliveiros - Original Message - From: Mark Roberts [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Sent: Wednesday, July 16, 2008 5:29 PM Subject: Re: [SQL] How to GROUP results BY month On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote: am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts folgendes: On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes: Howdy, all, I have a problem. I have a table which one of the fields is of type date. I need to obtain the totals of the other fields in a by-month basis IS there any easy way to do this using the GROUP BY or any other construct? ... group by extract(month from date) Andreas It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queries that span years. Right, but that wasn't the question... Honestly, the way the question was phrased, I'd have assumed that it wanted to group by month (not group by a group of months). Jan 08 is distinct from Jan 07. Please accept my sincerest apologies if you you feel that I misinterpreted the question. I was merely trying to illustrate the difference between what each approach was. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to GROUP results BY month
Howdy, all, I have a problem. I have a table which one of the fields is of type date. I need to obtain the totals of the other fields in a by-month basis IS there any easy way to do this using the GROUP BY or any other construct? Thanks in advance for your kind help Best, Oliveiros -- 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] This SQL works under Mysql, not Postgresql.
Neither have I. The LEFT JOIN I know is something like SELECT ... FROM table1 LEFT OUTER JOIN table2 ON Try using this construct Best, Oliveiros - Original Message - From: Scott Marlowe [EMAIL PROTECTED] To: acec acec [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Sent: Friday, January 25, 2008 4:23 PM Subject: Re: [SQL] This SQL works under Mysql, not Postgresql. On Jan 25, 2008 10:11 AM, acec acec [EMAIL PROTECTED] wrote: I have the following sql, which works fine under mysql database: SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL as SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN SUBSCRIBER s ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE suv, SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND sus.SERVICE_ID = 1) WHERE s.TELEPHONE = '1'; When I ran it under postgresql, which gave me ERROR: syntax error at or near It looks like I could not put two table on LEFT JOIN: LEFT JOIN (SERVICE suv, SERVICE sus) Do you have any suggestion for this problem? Is that legal SQL? I've never seen anything like that before... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Obtaining the primary key of the record just inserted
Hello, All, I have the need to know the primary key assigned to a record I've just INSERTed . Is there an easy way to solve this ? Similar to SQLServer's SELECT scope_identity() ; ? Any help deeply appreciated Best, Oliveiros
Re: [SQL] Obtaining the primary key of the record just inserted
It worked perfectly.
Thanks a million, Scott.
Best,
Oliveiros
- Original Message -
From: Scott Marlowe [EMAIL PROTECTED]
To: Oliveiros Cristina [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Sent: Thursday, November 08, 2007 6:03 PM
Subject: Re: [SQL] Obtaining the primary key of the record just inserted
On Nov 8, 2007 11:56 AM, Oliveiros Cristina
[EMAIL PROTECTED] wrote:
Hello, All,
I have the need to know the primary key assigned to a record I've just
INSERTed .
Is there an easy way to solve this ?
Similar to SQLServer's SELECT scope_identity() ; ?
In 8.2 and up:
insert into table a (info) values ('abc') returning id;
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] JOIN
Hey, Loredana.
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears
on count for every record whose date is in dates column, is this correct?
But in what record's dates column? On all of them? Or just the dates column
of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for
ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Thanx in advance
Best,
Oliveiros
2007/6/5, Loredana Curugiu [EMAIL PROTECTED]:
Any help, please?
On 6/5/07, Loredana Curugiu [EMAIL PROTECTED] wrote:
Hi everybody,
I have the following table:
count | theme | receiver| date
| dates
|
---+---+--++-+---
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
I want to add up the count column grouped by theme and receiver for the
dates included in the dates column.
So I have the following query:
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
The result of the query is:
sum | theme |receiver|
dates
---+---+--+
3 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
3 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
18 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
4 | LIA | +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
6 | LIA | +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
10 | LIA | +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
The result is wrong. I don't know what it is wrong at my query.
Please help.
Best,
Loredana
--
O Quê? SQL Server 2005 Express Edition? for
free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma
fortuna, homem!
Re: [SQL] JOIN
Hey, Loredana.
Nice to see you too ;-)
Thank you for your detailed clarifications.
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY() returns
true if B.date is on the vector A.dates, is this correct??
Loredane, Then please let me hear bout the result
Best,
Oliveiros
2007/6/5, Loredana Curugiu [EMAIL PROTECTED]:
On 6/5/07, Loredana Curugiu [EMAIL PROTECTED] wrote:
On 6/5/07, Oliveiros Cristina [EMAIL PROTECTED] wrote:
Hey, Loredana.
Hi Oliveiros! Nice to see you again!
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that
appears on count for every record whose date is in dates column, is this
correct?
Yap.
But in what record's dates column? On all of them? Or just the dates
column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct
output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver| date
|dates 2 | LIA | +40741775621 | 2007-06-02
00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
We can see that for LIA if we sum the count per day we have the
following:
themedate count
LIA 2007-06-01 00:00:00+004
LIA 2007-06-02 00:00:00+002
LIA 2007-06-03 00:00:00+002
LIA 2007-06-04 00:00:00+002
Also for CRIS:
themedate count
CRIS 2007-06-01 00:00:00+003
CRIS 2007-06-02 00:00:00+001
CRIS 2007-06-03 00:00:00+001
CRIS 2007-06-04 00:00:00+003
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver |
dates
-+--+-+
8 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA| +40741775621 |
{2007-06
Re: [SQL] JOIN
Hello again, Loredana.
Additional information required :-)
imagine the following situation
1| LIA | recv1 | date1 | (date1,date2)
2|LIA | recv1 |date 1 | (date2,date3)
3| LIA | recv1 | date1 | (date1,date3)
Should this yield 6? Or 4?
date 1 is not on the second dates column, but it is on the remaining two
Cheers,
Oliveiros
- Original Message -
From: Loredana Curugiu
To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org
Sent: Tuesday, June 05, 2007 3:15 PM
Subject: Re: [SQL] JOIN
On 6/5/07, Loredana Curugiu [EMAIL PROTECTED] wrote:
On 6/5/07, Oliveiros Cristina [EMAIL PROTECTED] wrote:
Hey, Loredana.
Hi Oliveiros! Nice to see you again!
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that
appears on count for every record whose date is in dates column, is this
correct?
Yap.
But in what record's dates column? On all of them? Or just the dates
column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output
for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver| date |
dates
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
We can see that for LIA if we sum the count per day we have the following:
themedate count
LIA 2007-06-01 00:00:00+004
LIA 2007-06-02 00:00:00+002
LIA 2007-06-03 00:00:00+002
LIA 2007-06-04 00:00:00+002
Also for CRIS:
themedate count
CRIS 2007-06-01 00:00:00+003
CRIS 2007-06-02 00:00:00+001
CRIS 2007-06-03 00:00:00+001
CRIS 2007-06-04 00:00:00+003
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-+--+-+
8 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5
Re: [SQL] JOIN
And , still, in your query, you are grouping by A.dates... is there any reason
for this that I am missing ?
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
If the dates column works as a discriminator to see if the row should be
considered or not,
maybe this would work
SELECT SUM(count), theme,receiver,date
FROM my_table
WHERE date=ANY(dates)
GROUP BY theme,receiver,date ;
But I don't know, do you need to include the column dates on output ?
Best,
Oliveiros
From: Loredana Curugiu
To: Oliveiros Cristina ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org
Sent: Tuesday, June 05, 2007 3:46 PM
Subject: Re: [SQL] JOIN
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
Doesn't work. I get the result
sum | theme | receiver | dates
-++--+
3 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA| +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
2 | LIA| +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA| +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA| +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
Which is not correct. The wrong values ( red colored ) remain as before
adding the clause. And now it is summed the counter's values per day
( first day of dates array ).
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY()
returns true if B.date is on the vector A.dates, is this correct??
Correct.
Regards,
Loredana
