[SQL] About Div
Dear All,I have a problem with this case :I have 10 candy for 7 child (A, B, C, D, E, F, G).Table X :code value --- A 0 B 0 C 0 D 0 E 0 F 0 G 0 And I want divide it with this algorithm : A = 10 / 7 = 1 B = (10-1) / (7-1) = 9 / 6 = 1 C = (10-2) / (7-2) = 8 / 5 = 1 D = (10-3) / (7-3) = 7 / 4 = 1 E = (10-4) / (7-4) = 6 / 3 = 2 F = (10-6) / (7-5) = 4 / 2 = 2 G = (10-8) / (7-6) = 2 / 2 = 2In my first solution i use loop - for each record in my function.But it is too slow in a lot of data.Did postgresql have a function for my case?Thanks All.-- "He who is quick to become angry will commit folly, and a crafty man is hated" Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta.
Re: [SQL] About Div
On 7/25/06, Otniel Michael <[EMAIL PROTECTED]> wrote: Dear All,I have a problem with this case :I have 10 candy for 7 child (A, B, C, D, E, F, G).Table X :code value --- A 0 B 0 C 0 D 0 E 0 F 0 G 0 And I want divide it with this algorithm : A = 10 / 7 = 1 B = (10-1) / (7-1) = 9 / 6 = 1 C = (10-2) / (7-2) = 8 / 5 = 1 D = (10-3) / (7-3) = 7 / 4 = 1 E = (10-4) / (7-4) = 6 / 3 = 2 F = (10-6) / (7-5) = 4 / 2 = 2 G = (10-8) / (7-6) = 2 / 2 = 2In my first solution i use loop - for each record in my function. But it is too slow in a lot of data.Did postgresql have a function for my case? No loop necessary. This is a simple math problem:dividedamount := candy / childcount;extra = candy % childcount; So the first (childcount - extra) get (dividedamount) pieces of candy and the last (extra) get (dividedamount + 1) pieces of candy.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
[SQL] Storage of Binary Data
I have a somewhat philosophical question about best practices when storing binary data.For my web projects, we have been storing binary data (like images, PDF's, etc) on the hard drive of the web server instead of the database. Within the database, we keep information, like whether an image is present, in a separate column. For example:CREATE TABLE site_user ( site_user_id BIGSERIAL NOT NULL, has_profile_image BOOLEAN DEFAULT false NOT NULL CONSTRAINT site_user_pkey PRIMARY KEY (site_user_id));And then store the profile image at: [web root]/images/siteuserprofile/profile-[site_user_id here].jpgWe also have a back end process that then checks these files into CVS to help maintain version control.My questions are:* What other options are people using to store data like this? * What are the advantages/disadvantages of these storage methods?I have been very reluctant to store the binary data in the database for the following reasons:* Performance because you cannot just save the data to the file system, you must interact with the database to get/save contents* Increases complexity of application* I have had problems with saving binary data using JDBC before (that was with Oracle 7 though)* I am concerned about how well the data backups will work - there have been several people who have had problems with backing up and restoring binary data on this listI am trying to develop some best practices so any input you guys have is welcome!Thanks== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] SQL generator
1) If you don't know how to write the SQL for this, then you might want to ask yourself if you have sufficient expertise to write a tool which generates such queries. 2) I have seen many attempts at query generators. I have yet to see a design which achieves a good balance between simplicity and flexibility. The fundamental problem, as near as I can tell, is that many people just don't think that way. Filters similar to what you can see in iTunes are about the best I've seen. I've never seen anything that attempts to handle aggregates. Unless you really get relational models, it's not even possible to sufficiently formulate your query. If you do then learning how to write a query in SQL just isn't that much more work. 3) Your example query doesn't even begin to imply what the underlying schema would be. Since SQL is pretty tightly coupled to the schema upon which it operates, this is a serious ommission. It's like asking for directions when you know where you want to go, but can't describe where you currently are. 4) I do not understand what the "aggregate functions" in your example are intended to calculate. Drew Bit Byter wrote: > Hi, > > This may be a little OT, but I don't know exactly where else to post > it. I am writing a little utility that generates valid SQL using > "English like" text. I want to use this to allow non-technical users to > be able to quickly write their own queries, to search through a > database that stores information about the sales of different > companies. I can provide more information for anyone who wants to help. > > Currently, the syntax is : > > Select ALL PRODUCT_FILTER from COMPANY where funcname(params) conditon > ... and ITEM_DATE date_condition > > > Where: > > product_filter specifies the product type to be included in the search > company specifies the company whose data is to be searched > funcname is an aggregate function > condition specifies the criteria for the aggregate function (i.e. a > HAVING clause) > date_condition specifies the criteria for the dates to be used in the > search > > Note: there can be more than 1 aggregate function > > A typical query then may look like this: > > Select all 'toys' from 'Walmart' where average_sales(100) > 10 and > avaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and > "01-Jan-06" > > > I would like to know what the underlying SQL statement will look like, > so that I can use this knowlege to build a generic parser that creates > SQL statements from the "English like" text, using the syntax I > described above. ---(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] reusing AS
Gregory Stewart wrote: > I am trying to do something like this: > > SELECT SUM(sales_today) AS sales_today_total, SUM(sales_lastweek) AS > sales_lastweek_total > CASE WHEN sales_today_total = '0' THEN '0'::int4 WHEN sales_lastweek_total = > '0' THEN '0'::int4 ELSE ((100/sales_today_total*sales_lastweek_total)-100) > END AS variance > FROM mytable > > I am getting the message that the column 'sales_today_total' and > 'sales_lastweek_total' do not exist. It looks like I can't reference the > aliases I defined (sales_today_total, sales_lastweek_total). That is correct. Use a sub-query: SELECT sales_today_total, sales_lastweek_total, CASE WHEN sales_today_total = 0 THEN 0 WHEN sales_lastweek_total = 0 THEN 0 ELSE ((100/sales_today_total*sales_lastweek_total)-100) END AS variance FROM (SELECT SUM(sales_today) AS sales_today_total, SUM(sales_lastweek) AS sales_lastweek_total FROM yourtable); > The reason I am asking is my actual SQL query is rather long with a few > dozen SUM functions, most of them being reused within the query for > calculations. I just don't want Postgresql to calculate the same thing over > and over again and getting the same results and wasting resources. It won't if you 1) Don't use volatile functions (obviously) 2) Don't force recalculation by correlating your sub-queries. Drew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL generator
"Bit Byter" <[EMAIL PROTECTED]> writes: > I would like to know what the underlying SQL statement will look > like, so that I can use this knowlege to build a generic parser that > creates SQL statements from the "English like" text, using the > syntax I described above. I seem to recall that Lotus tried doing this once upon a time with trying to build an "english-like" interface for doing "business intelligence-like" queries against spreadsheets. Nobody likely remembers HAL; it was underwhelmingly useful. In effect, anyone that was skilful enough at composing "English-like" queries to get HAL to work was likely to find the "native" methods more convenient. -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://cbbrowne.com/info/linuxxian.html "La Cicciolina [...] Electing her was an interesting contrast to the situation in the UK: In Italy they elect a representative from the sex industry. In the UK, they elect their clients." -- Peter Gutmann ---(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] About Div
Mr. Aaron. I am sorry, your solution didn't match in my case. Example for your solution :A = 1B = 1C = 1D = 1E = 1F = 1G = 4G have 4 candy. Its too much for G.In my case, the solution is :A = 1B = 1C = 1D = 1E = 2F = 2G = 2The extra candy is given to three child.Do you have the other solution? I need function in postgresql for my case.Because my loop is too slow.Btw thanks for your solution.Aaron Bono <[EMAIL PROTECTED]> wrote: On 7/25/06, Otniel Michael <[EMAIL PROTECTED]> wrote: Dear All,I have a problem with this case :I have 10 candy for 7 child (A, B, C, D, E, F, G).Table X :code value --- A 0 B 0 C 0 D 0 E 0 F 0 G 0 And I want divide it with this algorithm : A = 10 / 7 = 1 B = (10-1) / (7-1) = 9 / 6 = 1 C = (10-2) / (7-2) = 8 / 5 = 1 D = (10-3) / (7-3) = 7 / 4 = 1 E = (10-4) / (7-4) = 6 / 3 = 2 F = (10-6) / (7-5) = 4 / 2 = 2 G = (10-8) / (7-6) = 2 / 1 = 2In my first solution i use loop - for each record in my function. But it is too slow in a lot of data.Did postgresql have a function for my case? No loop necessary. This is a simple math problem:dividedamount := candy / childcount;extra = candy % childcount; So the first (childcount - extra) get (dividedamount) pieces of candy and the last (extra) get (dividedamount + 1) pieces of candy.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com== -- "He who is quick to become angry will commit folly, and a crafty man is hated" __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [SQL] About Div
Otniel Michael wrote: Mr. Aaron. I am sorry, your solution didn't match in my case. Example for your solution : A = 1 B = 1 C = 1 D = 1 E = 1 F = 1 G = 4 G have 4 candy. Its too much for G. In my case, the solution is : A = 1 B = 1 C = 1 D = 1 E = 2 F = 2 G = 2 The extra candy is given to three child. Do you have the other solution? I need function in postgresql for my case. Because my loop is too slow. Would this achieve the result you're looking for, or must it be done with a function? Assuming you already know the value of totalchildren and totalcandy: BEGIN -- Everyone gets at least this number, which could be zero or more. UPDATE X SET value = (totalcandy / totalchildren); -- Hand the remainder out one at a time until all are gone. UPDATE X SET value = (value + 1) WHERE code = (SELECT code FROM X ORDER BY code DESC LIMIT (totalcandy % totalchildren)); COMMIT Ross ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Storing an ordered list
What is the best way to store and ordered list that can be updated OLTP-style? A simplified problem is that I have an event, and the event has an ordered list of predicates and I need to preserve the order of the predicates. All of the data is entered via a web application, and I would like to support the new flashy ajax drag-droppy thingies, meaning that there could be a significant amount of updates if the user is dragging things all over the place. I figure that one choice is to explicitly code the order as an integer column in the predicate table which has the advantage of being very easy and fast to query/order but *very* slow to reorder as all of the predicates need to be updated. This would seem to be a postgres/MVCC weak spot as well. Example: create table event (event_id integer); create table predicate (event_id integer not null references event(event_id), name varchar, order integer); insert into event (event_id) values (1); insert into predicate (1, 'first event', 1); insert into predicate (1, 'second predicate', 2); select * from predicate p where p.event_id = 1 order by p.order; I'm also thinking about a linked list, i.e. create table event (event_id integer); create table predicate (predicate_id integer, event_id integer not null references event(event_id), name varchar, next_predicate integer references predicate (predicate_id)); insert into predicate (101, 1, 'second predicate', NULL); insert into predicate (102, 1, 'first predicate', 101); The downside is that I'm not quite sure how to efficiently query the linked list. Any suggestions? Are there any known best practices for storing ordered lists in relational databases? Are there any tricks that I can use with postgres? Thanks -Mike ---(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] SQL generator
> A typical query then may look like this: > > Select all 'toys' from 'Walmart' where average_sales(100) > 10 and > avaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and > "01-Jan-06" See Microsoft's English Query for SQL Server or the SQ-HAL project for examples: http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part9/c3261.mspx?mfr=true http://www.csse.monash.edu.au/hons/projects/2000/Supun.Ruwanpura/ In general, it's easier to write the SQL than to optimize natural language-based query translation. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
