| |
| |
| |
|
Page: 1 2 3 4
Comments:
<||cw> um, doesn't sqlite people tell us that sqlite falls flat on its face at ~10,000 rows or some such puny number?
<JanniCash> you think he would have listened to that?
<||cw> i've used it once in code I wrote, probably never use it again
<||cw> the way they do their engine is totaly not portable
<||cw> it barely qualifies as sql at all
<JanniCash> I looked a bit at the v3 docs ... lots of Postgres inspired things (vacuum, explain, analyze) ... but somehow the fundamental Postgres philosophy got lost
<||cw> it does perform acceptably for small, mostly static, datasets but then I probably could have made a csv type thing that would have done as well
<||cw> i thing I used v2
<JanniCash> I think they kicked out the underlying gdbm database between 2 and 3
<JanniCash> now they claim to be crash safe and ACID
<JanniCash> they even have triggers ... although triggers in their case can only have insert/update/delete statements, no local variables, no if/then/else, no loops ... hmmm
<JanniCash> tastes kinda like gravy without the meat
<ndnet> Welp, this may not be the most relevant place to ask, but I could use advice on simple database table layout. Either advice with the tables, or where I could ask for help, or even a simple 'scrap it all and start over'. I have two table descriptions pastebinned ( http://pastebin.com/627682 ), I'd just like someone more experienced to eyeball it and maybe give a few pointers for improving its design before I work any further on it and possibly deeper into mu
<ndnet> ddling it up. ;)
<ndnet> Oops, hit the line length! :o
<albo> why does this take a long time update ads as a join snap as ap set a.nextreport=ap.nextreport,a.lastreport=ap.lastreport where ap.adtype='web'
<albo> but select count(*) from snap where adtype='web' takes microseconds
<outofrange> Two questions about this paste: http://nopaste.snit.ch:8001/6814 ...
<outofrange> First, is there a way to calculate the percentage using just the first view? Second, is there any difference placing the WHERE inside the FROM (r.cl*** = c.id and c.cl***_time > now() ) ?
<jackfig> how would you place the WHERE inside the FROM??
<outofrange> LEFT OUTER JOIN registration r ON ( r.cl*** = c.id and c.cl***_time > now - interval '1 day' )
<jackfig> that's dumb
<jackfig> it's not a join condition
<jackfig> it's a predicate
<jackfig> it does NOT belong there
<outofrange> Ok.
<outofrange> How about the percent?
<jackfig> ;you ARE calculating the percentage using the FIRST VIEW
<jackfig> look at your code
<outofrange> I mean have a percentage column in the first view and not bother with the second view.
<jackfig> that's a different question
<outofrange> Yes, "using just the first view" was not clear.
<jackfig> and yes, but you can't use the column alias
<jackfig> sum (CASE WHEN r.reg_status = 1 THEN 1 ELSE 0 END)/c.cl***_size as percent
<jackfig> should work
<outofrange> jackfig: works great. Thanks.
<albo> is there a way to "store" a variable in the select part and then use it in the where part
<Yango> select col1, col2, col1+col2 where col1+col2 < 23
<Yango> ?
<Yango> usually storage is not allowed in DBMS's
<ndnet> This may not be the most relevant place to ask, but I could use advice on simple database table layout. Either advice with the tables, or where I could ask for help, or even a simple 'scrap it all and start over'. I have two table descriptions pastebinned ( http://pastebin.com/627923 ), I'd just like someone more experienced to eyeball it and maybe give a few pointers for improving its design before I muddle it up even further ;)
<danut007ro> yo people, where can I post a picture with a database schema, so maybe somebody can take a look at it and tell me what is wrong? please...
<jackfig> [19:49] <danut007ro> yo people, where can I post a picture with a database schema, so maybe somebody can take a look at it and tell me what is wrong? please... <=== pastebin.com
<danut007ro> man, I need to post a picture from
<danut007ro> microsoft sql server management studio
<HolidayR2mbler> imageshack?
<danut007ro> it's a view, I think you will understand better from that...
<HolidayR2mbler> Try one of the many popular image hosting services.
<danut007ro> just one moment
<danut007ro> here it is : http://img57.imageshack.us/my.php?image=clipboard014dd.jpg
<danut007ro> the problem is that after adding the dest table, nothing is selected anymore
<jackfig> ???
<danut007ro> I know :)
<jackfig> then you have data issues you need to resolve
<danut007ro> the data is almost empty, just one record per table or so, to test it
<danut007ro> I think I got the data right
<jackfig> apparently not
<jackfig> as you have no matching entries in the dest table
<danut007ro> is ok if suc_id referenced in dest also ?
<jackfig> why wouldn't it be?
<jackfig> select count(*) From conv where dest_id in (select dest_id from dest)
<jackfig> see if you get anything
<danut007ro> I think I got it...
<danut007ro> thanks !
<danut007ro> is a NULL in conv.dest_id
<HolidayR2mbler> I'm doing Oracle DBA II 9i training this week.
<HolidayR2mbler> I don't think a single human on the planet can explain Oracle local naming.
<HolidayRambler> Much less the instructor.
<sonius> I have an osql backup/restore question, can I ask that here?
<HolidayRambler> Sure.
<sonius> can I restore a backup to a database witha different name?
<HolidayRambler> I think so.
<jackfig> can you tell us which dbms you're using?
<HolidayRambler> osql = MSSQL
<sonius> mssql destop edition
<jackfig> sonius: define 'backup'
<HolidayRambler> One would expect, "BACKUP DATABASE TO FILE..."
<HolidayRambler> Something along those lines.
<jackfig> one NEEDS to ask
<sonius> osql command; >backup database
<sonius> >to disk="c;/temp/backup.dat"
<sonius> >go
<jackfig> you need to look in Books Online on backup and restore
<sonius> the internet is a rich source of information, no doubt, but it doesnt compare to the personal touch one can receive by asking a question in a forum like this
<jackfig> you clearly don't understand I don't mean the INTERNET
<jackfig> Books OnLine == SQL SERVER DOCUMENTATION
<jackfig> www.microsoft.com/sql
<jackfig> download it
<sonius> thanks jackfig , ill give it a buRRRl :)
<rubyruy> I have a really hair problem that may or may not actually be dealt-with-able by SQL... I'm not so sure any more.
<rubyruy> Basically I have a table full of Orders
<rubyruy> They can be buy orders or sell orders (indicated by a boolean)
<rubyruy> Each order has a certain product_id it is referring to
<rubyruy> Actually I should expand a bit on what the Orders table's fields are: product_id, is_bid (if true this is a buy order, if false this is a sell order), price (the price being bidded/offered) and volume (how many units someone is willing to buy/sell for this price)
<rubyruy> What I need here is a list of the most profitable trades for each product. I.e. output a list of all products, for each product specifying the difference between that product's highest buy order and lowest sell order - sorted DESC by that difference. Whew.
<rubyruy> Even if this can't be done with pure sql alone and I have to cycle through each product at the application level, I'll also take any suggestions for how to minimize the sql queries per cycle.
<rubyruy> Also, eventually what this needs to do is find the most profitable 'trade' of all products listed, remove that trade and then repeat the process until a certain quota is met.
<rubyruy> It seems like this is the sort of problem that has been dealt with several times over already but I'm not sure how to formulate it into something google could understand.
<rubyruy> Anyway - basically i'd like to know how/if this can be done in a way that won't take half a day to compute.
<AqD> hi rubyruy!
<HolidayRambler> You mean biggest diff between buy and sell for a single order?
<HolidayRambler> Or for ALL orders?
<rubyruy> of ALL orders
<HolidayRambler> That makes no sense.
<rubyruy> but within the constraint that the buy and sell order share a product.id
<rubyruy> er product_id
<HolidayRambler> Do you see why it makes no sense to me?
<HolidayRambler> Suppose you buy at $1.00 and sell at $1.10 one day.
<HolidayRambler> Then the next, buy at $2.00 and sell at $2.05.
<HolidayRambler> Is the diff there $1.05?
<HolidayRambler> Or $0.10?
<rubyruy> in day 1 it is 0.10, in day 2 it would be .05
<HolidayRambler> Right, so you are making no sense in how you stated it.
<karat> is "on duplicate key" insert syntax standard?
<rubyruy> but ***ume price aren't changing
<karat> I see it for mysql but don't recall seeing that ever for postgres
<HolidayRambler> rubyruy: You want to maximize the difference over all orders.
<HolidayRambler> That's not out of the bounds of SQL at all.
<rubyruy> hey - that's good to hear
<HolidayRambler> select max(sell - buy) from foo group by product_id
<HolidayRambler> or something like that.
<rubyruy> oh jesus i can't believe i didn't think of that *slap*
<HolidayRambler> Depending on what the RDBMS supports.
<rubyruy> i had like 5 level deep nested subqueries by now - hahah
<rubyruy> oh wait wait wait
<rubyruy> there is no 'sell' or 'buy'
<HolidayRambler> Right; whatever you have that's relevant.
<rubyruy> I think an example is in order - 1 sec
<HolidayRambler> If the table isn't that simple, then you'll have to do more work.
<HolidayRambler> table(s).
<rubyruy> Sample Records (price, buyer/seller,product, seller/buyer name): ($2,buyer,widgets, John), ($2.50, buyer,widgets, Susan),($1, seller, widgets, Todd),($1.50, seller,widgets,Jane), ($20, buyer, doodads,Tim), ($25, seller, doodads,Liz),($10, seller, doodads, Ken), ($15, seller, doodads, Kim)
<rubyruy> Correct Results (note order of highest spread first):Buy from Ken and sell to Tim for $15 spread, Buy widgets from Todd and sell to Susan for $1.50 spread;
<rubyruy> I can make more legible tables to paste on a public pasteboard if you want
<HolidayRambler> What keeps track of the transactions themselves?
<rubyruy> nothing - it's a snapshot of the market
<HolidayRambler> Hm.
<HolidayRambler> Then there's no way to get the info you want.
<rubyruy> when a new snapshop is taken the old one is removed (well archived)
<rubyruy> Well I can have the importer do some extra work if that's what you are hinting at
<HolidayRambler> If you have no way to tie a buyer to a seller for a particular transaction, then there is no way to maximize that number.
<rubyruy> How do you mean? We can tie them together by product.
<HolidayRambler> Yes, but products don't uniquely identify transactions.
<HolidayRambler> Do they?
<rubyruy> What exactly do you mean by transactions? I have a feeling you aren't talking about the lay-person meaning
<HolidayRambler> I am exactly talking about that.
<HolidayRambler> If Joe sells something to Jane, there's a transaction.
<rubyruy> That doesn't affect me though... The table need not simulate actual transactions - just find the highest theoretical ones.
<HolidayRambler> The highest POSSIBLE transactions?
<rubyruy> right
<HolidayRambler> OK, that's something you can solve.
Return to
#sql
or
Go to some related
logs:
eselect error unrecognized option ati
devices.map grub
xmlrpclib segfault
inkscape add fonts
#perl
yaa autoresponder helo
vmware fedora network lspci vmxnet
ensoniq ekiga
#perl
definition of an ostrege
|
|
