[SQL] Query aid
Hi all, I have a table acct as (username, terminatedate, terminatecause) I would like to build a query which returns three columns orderd by data like: date_trunc( 'day', terminatedate ) | count(cause1) | count(cause2) where cause1/2 are two type of termination cause from the field terminatecause. for example acct table could be: user1|01/01/2004 01:01:01| error user2|01/01/2004 01:02:01| error user1|01/01/2004 02:00:01| normal user3|02/01/2004 10:00:01| normal user2|02/01/2004 10:10:01| error I would like to obtain: date |normal| error 01/01/2004| 1 | 2 02/01/2004| 1 | 1 Thanks in advance. Roberto Fichera. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [despammed] [SQL] question about index
am 16.12.2004, um 11:41:54 +0100 mailte Jerome Alet folgendes: > Hi, > > For a future databas, I plan to have got a table with a text field > which can contain only three different values, say "VALUE1", > "VALUE2", and "VALUE3" Why text-fields for this task? I would prefer a smallint for this with a check-contraint to check the values. > be as fast as possible. > > considering that almost 70% of the rows will be with 'VALUE1', 20% > will be with 'VALUE2' and 10% will be with 'VALUE3' on the average. > > should I create an index to speedup the counts or not ? Yes. Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Tel. NL Heynitz: 035242/47212 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Query aid
Am Donnerstag, 16. Dezember 2004 11:34 schrieb Roberto Fichera: > Hi all, > > I have a table acct as (username, terminatedate, terminatecause) > I would like to build a query which returns three columns orderd by data > like: > > date_trunc( 'day', terminatedate ) | count(cause1) | count(cause2) > > where cause1/2 are two type of termination cause from the field > terminatecause. > > for example acct table could be: > > user1|01/01/2004 01:01:01| error > user2|01/01/2004 01:02:01| error > user1|01/01/2004 02:00:01| normal > user3|02/01/2004 10:00:01| normal > user2|02/01/2004 10:10:01| error > > I would like to obtain: > > date |normal| error > 01/01/2004| 1 | 2 > 02/01/2004| 1 | 1 try something like this: SELECT date_trunc( 'day', terminatedate ) AS day, SUM( CASE WHEN cause = 'error' THEN 1 ELSE 0 END ) AS error_count, SUM( CASE WHEN cause = 'normal' THEN 1 ELSE 0 END ) AS normal_count, FROM acct AS acct1 GROUP BY day ORDER BY day ASC; kind regards, janning ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] question about index
On Thu, 16 Dec 2004 11:41:54 +0100 Jerome Alet <[EMAIL PROTECTED]> wrote: > For a future databas, I plan to have got a table with a text field > which can contain only three different values, say "VALUE1", > "VALUE2", and "VALUE3" Can it increase? That is, can a "VALUE4" be added half way through the year? > this table may have, over the course of one year, several million > rows for a size around 2 Gb or more. Doing a COUNT(*) on that many rows could get expensive but... > I'd be interested in having : > > SELECT count(*) AS nbvalue1 FROM mytable WHERE > myfield='VALUE1'; SELECT count(*) AS nbvalue2 FROM mytable > WHERE myfield='VALUE2'; SELECT count(*) AS nbvalue3 FROM > mytable WHERE myfield='VALUE3'; First of all, consider doing it in one statement so that you at least are not running through it multiple times. SELECT myfield, COUNT(*) FROM mytable GROUP BY myfield; Second, consider rules or triggers to keep a separate table up to date at all times so that you simply have to dump a three row table instead of going through a huge table counting as you go. We did that in a similar situation and it made a huge difference. We were doing SUM() instead of COUNT() and we were calculating an extremely small percentage of the table - average probably 3 or 4 rows out of 20 million on average - but it still was worth our while to calculate the sum (balance) on every transaction rather than calculate it every time. You need to analyze your own data and usage but this may be a better solution for you. As usual, if not completely satisfied you get a full refund. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query aid
At 12.21 16/12/2004, you wrote: Am Donnerstag, 16. Dezember 2004 11:34 schrieb Roberto Fichera: > Hi all, > > I have a table acct as (username, terminatedate, terminatecause) > I would like to build a query which returns three columns orderd by data > like: > > date_trunc( 'day', terminatedate ) | count(cause1) | count(cause2) > > where cause1/2 are two type of termination cause from the field > terminatecause. > > for example acct table could be: > > user1|01/01/2004 01:01:01| error > user2|01/01/2004 01:02:01| error > user1|01/01/2004 02:00:01| normal > user3|02/01/2004 10:00:01| normal > user2|02/01/2004 10:10:01| error > > I would like to obtain: > > date |normal| error > 01/01/2004| 1 | 2 > 02/01/2004| 1 | 1 try something like this: SELECT date_trunc( 'day', terminatedate ) AS day, SUM( CASE WHEN cause = 'error' THEN 1 ELSE 0 END ) AS error_count, SUM( CASE WHEN cause = 'normal' THEN 1 ELSE 0 END ) AS normal_count, FROM acct AS acct1 GROUP BY day ORDER BY day ASC; Many thanks! This works well :-)! kind regards, janning Roberto Fichera. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [despammed] question about index
On Thu, Dec 16, 2004 at 12:01:39 +0100, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > > > > considering that almost 70% of the rows will be with 'VALUE1', 20% > > will be with 'VALUE2' and 10% will be with 'VALUE3' on the average. > > > > should I create an index to speedup the counts or not ? > > Yes. Indexes will probably not help in this case. Even when looking at 10% of the rows, a sequential scan is still going to be faster than an index scan in most cases. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] plpgsql.. SELECT INTO ... WHERE FIELD LIKE
On Thu, Dec 16, 2004 at 05:53:43PM -0600, Yudie wrote: > How in plpgsql use LIKE with a variable? > > let say I want to do this query: > > SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1; > > keyword is a variable, in this case I want to find name like 'Jo%' Use the concatenation operator (||): SELECT ... WHERE firstname LIKE keyword || ''%'' LIMIT 1; Notice the two single quotes, which are necessary if the function body is surrounded by quotes. Life gets easier in 8.0 with dollar quoting. Are you planning to add more code to your PL/pgSQL function? If not, then you could replace it with a simple SQL function: CREATE OR REPLACE FUNCTION custlike(TEXT) RETURNS INTEGER AS ' SELECT id FROM customer WHERE firstname LIKE $1 || ''%'' LIMIT 1; ' LANGUAGE sql; The function will return NULL if it finds no records. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] plpgsql.. SELECT INTO ... WHERE FIELD LIKE
Try: SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || ''%'' LIMIT 1; You append KEYWORD and a '%' together using ||. You need to use doubled quotes inside the quoted environment; one gets stripped off so that the stored procedure will contain the query SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || '%'' LIMIT 1; -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://linuxfinances.info/info/sgml.html C is almost a real language. (see assembler) Even the name sounds like it's gone through an optimizing compiler. Get rid of all of those stupid brackets and we'll talk. (see LISP) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] plpgsql.. SELECT INTO ... WHERE FIELD LIKE
Oops! [EMAIL PROTECTED] ("Yudie") was seen spray-painting on a wall:
> How in plpgsql use LIKE with a variable?
>
> let say I want to do this query:
> SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1;
>
> keyword is a variable, in this case I want to find name like 'Jo%'
>
> CREATE OR REPLACE FUNCTION custlike(text) RETURNS INT4 AS'
> DECLARE
> keyword ALIAS FOR $1;
> RS RECORD;
> BEGIN
> SELECT INTO RS id FROM customer WHERE firstname like keyword% LIMIT 1;
> IF FOUND THEN
> RETURN RS.id;
> ELSE
> RETURN NULL;
> END IF;
> END'
> LANGUAGE 'PLPGSQL';
Try:
SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || ''%'' LIMIT
1;
You append KEYWORD and a '%' together using ||. You need to use
doubled quotes inside the quoted environment; one gets stripped off so
that the stored procedure will wind up containing the query:
SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || '%' LIMIT 1;
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/sgml.html
C is almost a real language. (see assembler) Even the name sounds like
it's gone through an optimizing compiler. Get rid of all of those
stupid brackets and we'll talk. (see LISP)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [NOVICE] FUNCTION, TRIGGER and best practices
Keith, > Can triggers and functions have the same name? Yes. > Is this a good practice? It can be. If the function does nothing but power the trigger, sure. Or you can use prefixes or suffixes to distinguish them. For example, if you had an audit trigger on the companies table, you might call the function audit_companies_tf, the trigger audit_companies_tg. > Where should the function and trigger be stored? Um, in the database? > In the same schema as the table they are connected to? I'm not sure you CAN put the trigger in a different schema from the table. It's a dependant object. > I will now be attempting to expand this function to move data from two > source tables to four or more target tables. (Different source columns go > to different tables.) Is it possible to do a transaction/commit/rollback > inside a function that is driven by a trigger so that if any portion of the > transfer fails it is all rolled back? Well, if any portion of a trigger fails, everything is rolled back; so is the insert, for a before trigger. So that might be the way to handle the double-trigger situation except they'll be in two different tables, yes? So that'll require an explicit transaction wrapping the two inserts. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Table History
Title: Message A database I maintain has a central transaction table ( FTRANS) I would like an second table FTRANS_IMAGE to maintain a historical view of ftrans. So if records in FTRANS are altered we can trace the effect of those changes in FTRANS_IMAGE. I expect this has been done MANY times and I wonder if a general purpose trigger exists or if not then can someone point me to an example set of triggers? I would like to trace an action code "Edit" or "Delete" in FTRANS_IMAGE in additional to the common fields. thanks muchly Richard Sydney-Smith --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.799 / Virus Database: 543 - Release Date: 19/11/2004
[SQL] question about index
Hi, For a future databas, I plan to have got a table with a text field which can contain only three different values, say "VALUE1", "VALUE2", and "VALUE3" this table may have, over the course of one year, several million rows for a size around 2 Gb or more. I'd be interested in having : SELECT count(*) AS nbvalue1 FROM mytable WHERE myfield='VALUE1'; SELECT count(*) AS nbvalue2 FROM mytable WHERE myfield='VALUE2'; SELECT count(*) AS nbvalue3 FROM mytable WHERE myfield='VALUE3'; be as fast as possible. considering that almost 70% of the rows will be with 'VALUE1', 20% will be with 'VALUE2' and 10% will be with 'VALUE3' on the average. should I create an index to speedup the counts or not ? any idea of the impact of running these three queries every 5 minutes on say 10 000 000 rows ? thanks in advance Jerome Alet ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [despammed] question about index
am 16.12.2004, um 8:10:25 -0600 mailte Bruno Wolff III folgendes: > On Thu, Dec 16, 2004 at 12:01:39 +0100, > Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > > > > > > considering that almost 70% of the rows will be with 'VALUE1', 20% > > > will be with 'VALUE2' and 10% will be with 'VALUE3' on the average. > > > > > > should I create an index to speedup the counts or not ? > > > > Yes. > > Indexes will probably not help in this case. Even when looking at 10% of > the rows, a sequential scan is still going to be faster than an index > scan in most cases. Okay. Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Tel. NL Heynitz: 035242/47212 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] plpgsql.. SELECT INTO ... WHERE FIELD LIKE
How in plpgsql use LIKE with a variable? let say I want to do this query: SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1; keyword is a variable, in this case I want to find name like 'Jo%' Full function: CREATE OR REPLACE FUNCTION custlike(text) RETURNS INT4 AS' DECLARE keyword ALIAS FOR $1; RS RECORD; BEGIN SELECT INTO RS id FROM customer WHERE firstname like keyword% LIMIT 1; IF FOUND THEN RETURN RS.id; ELSE RETURN NULL; END IF; END' LANGUAGE 'PLPGSQL';
