Re: [SQL] Question about GUI
Hş, On Tue, 2007-01-09 at 17:45 -0600, Judith wrote: > I just want to know if there is some GUI with postgreSQL for Fedore Core 3?? There is pgadmin 1.2.0 (old release, but it works) for Fedora Core 3: http://www.postgresql.org/ftp/pgadmin3/release/v1.2.0/fc3/ Recent versions of pgadmin3 won't work on Fedora Core 3. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[SQL] Requirement for PostgreSQL Database Developer
Hi , Location: San Diego, CA [You can also TeleCommute but you have to be on the client side for 2 Weeks in a month] Duration: 6+ months. This is Mark with ProV International, This email is in regards to the requirement we have with one of our direct client in San Diego, CA. Position :- PostgreSQL Database Developer This position involves creating tables, views, functions and stored procedures to support front end OLTP and reporting applications. The ideal developer will have thorough knowledge of SQL (PL/pgSQL), experience with at least one other PostgreSQL language (e.g. PL/Perl), and extensive experience with complex stored procedures, code optimization, and index tuning in PostgreSQL. Ideal candidate will have the following qualifications: 5+ years database development with PostgreSQL Must have experience with the replication tool called SIony-I in implementing failover redundancy with Slony-I and pgpool. Knowledge of at least one other language in addition to PL/pgSQL, such as PL/Perl or PL/Java. Experience implementing PostgreSQL replication . Some experience with either SQL Server 2000 or Oracle 9i/10g. Significant background in creating complex stored procedures and SQL scripts Understanding of database normalization concepts Some experience in logical and physical database design and implementation Prior experience working in a project oriented environment and meeting deadlines under tight time constraints Strong analytical skills Capable of working independently with minimal supervision. If you find yourself comfortable with this job profile & find it interesting please send me your resume in MS Word Format. thanks , Mark, ProV International Tampa, FL 33607 Tel 408 - 241 - 7795 Xtn - 27 [EMAIL PROTECTED] www.provintl.com
[SQL] How to aggregates this data
Now I've figured why I don't get replies, I'm sending again. Intro: I have a table that contains much data like this: SELECT distinct on (date) asx_code,date,open,high,low,close from sales_summaries where asx_code = 'TLSCA' and date >= '2006-12-01' order by date, time desc limit 10; SELECT distinct on (date) asx_code,date,open,high,low,close,volume from sales_summaries where asx_code = 'TLSCA' and date >= '2006-12-01' order by date, time desc limit 10; asx_code |date| open | high | low | close | volume --++---+---+---+---+-- TLSCA| 2006-12-01 | 2.330 | 2.340 | 2.300 | 2.300 | 29165222 TLSCA| 2006-12-04 | 2.310 | 2.320 | 2.280 | 2.300 | 14973667 TLSCA| 2006-12-05 | 2.310 | 2.320 | 2.300 | 2.320 | 23238175 TLSCA| 2006-12-06 | 2.320 | 2.400 | 2.320 | 2.380 | 58357650 TLSCA| 2006-12-07 | 2.380 | 2.450 | 2.380 | 2.450 | 64005594 TLSCA| 2006-12-08 | 2.450 | 2.520 | 2.450 | 2.510 | 82831560 TLSCA| 2006-12-11 | 2.500 | 2.550 | 2.500 | 2.540 | 38711749 TLSCA| 2006-12-12 | 2.560 | 2.620 | 2.560 | 2.610 | 61709325 TLSCA| 2006-12-13 | 2.600 | 2.600 | 2.540 | 2.590 | 39455386 TLSCA| 2006-12-14 | 2.600 | 2.650 | 2.600 | 2.620 | 30675340 (10 rows) Some may guess (correctly) that this is price information from a stock exchange. What I want to do is consolidate the data into weekly (for starters) periods. Writing a Java (or other procedural language) program to read row by row is fairly straightforward. What I want to do is to do it entirely in SQL. I've perused my book (Mastering SQL by Martin Gruber), the postgresql docs (I have versions here on RHEL (Centos) 4, FC5,6, Debian Testing - up to 8,1) and I don't see how to choose the entry for the first open column, the last close. Max, min and average will do nicely for the others, and converting the date to an interval then dividing by seven seems to work nicely for creating my groups, The results I expect from the above data are TLSCA 2006-12-07 2.330 2.450 2.280 2.450 mumble TLSCA 2006-12-14 2.450 2.650 2.450 2.620 mumble The question is, "How can I do this in SQL?" May the pedants ignore that these weeks don't run Monday to Friday:-) fwiw I'm working on Linux; thus far everything's done in bash scripts aided by friends such as lynx, grep awk and (Of course) psql. ---(end of broadcast)--- TIP 1: 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] How to aggregates this data
John,
Sub-selects to the rescue. See below.
select s1.asx_code, s1.bdate AS date, s1.low, s1.high, s2.open, s3.close,
s1.volume
from (select asx_code, date_trunc('week', date) AS bdate, max(date) AS
edate, min(low) AS low, max(high) AS high, sum(volume) AS volume
from sales_summaries
group by asx_code, date_trunc('week', date)) s1, sales_summaries s2,
sales_summaries s3
where s1.bdate = s2.date
and s1.asx_code=s2.asx_code
and s1.edate = s3.date
and s1.asx_code=s3.asx_code;
asx_code | date | low | high | open | close |
volume
++--+--+--+---+---
TLSCA | 2006-12-04 00:00:00-05 | 2.28 | 2.52 | 2.31 | 2.51 |
243406646
TLSCA | 2006-12-11 00:00:00-05 | 2.5 | 2.65 | 2.5 | 2.62 |
170551800
The "date" is based on ISO-8601 (in other words the week starts on Monday).
Be warned, as it is questionable how this will scale. It may require
expression (function based) indexes.
Oracle has a feature called analytic functions, which would allow you to use
functions such as LEAD, LAG, FIRST_VALUE, LAST_VALUE. In particular
FIRST_VALUE and LAST_VALUE would have been useful to determine the open and
close for a week, but before analytics in Oracle you would use sub-selects
or multiple joins.
Re: [SQL] How to aggregates this data
By the way, if you want to include that "incomplete" week before 12/1
(incomplete because it doesn't have a data point for Monday) then you would
do this:
select s1.asx_code, s1.wdate AS date, s1.low, s1.high, s2.open, s3.close,
s1.volume
from (select asx_code, date_trunc('week', date) AS wdate, min(date) AS
bdate, max(date) AS edate, min(low) AS low, max(high) AS high, SUM(volume)
AS volume
from sales_summaries
group by asx_code, date_trunc('week', date)) s1, sales_summaries s2,
sales_summaries s3
where s1.bdate = s2.date
and s1.asx_code=s2.asx_code
and s1.edate = s3.date
and s1.asx_code=s3.asx_code;
