Angband.oook.cz
Angband.oook.cz
AboutVariantsLadderForumCompetitionComicScreenshotsFunniesLinks

Go Back   Angband Forums > Angband > Development

Reply
 
Thread Tools Display Modes
Old August 31, 2011, 16:37   #11
Azerath
Rookie
 
Join Date: Jun 2011
Posts: 21
Azerath is on a distinguished road
1. When you want to get rectangular table instead of flat list of records, usually databases support such functionality search for (PIVOT in SqlServer; CrossTab in postgress, etc)

Summarizing instread of getting this (flat list of records):
Action1 Ring 123
Action1 Sword 45
Action2 Ring 112
Action2 Sword 55
Action3 Ring 101
Action3 Sword 33

get this (rectangular result):
Action Ring Sword
Action1 123 45
Action2 112 55
Action3 101 33

2. Excluding can be done like in C/C++ AND (... OR ... OR ...), but exclusion from some set use IN or NOT IN

SELECT COUNT(1) FROM Abc WHERE (Level BETWEEN 1 AND 100) AND Kind NOT IN ('Ring', 'Sword')

EDIT:

3. Rectangular result can be achieved using sub queries if are supported by DB (maybe not fastest way, but will work):
[example from http://stackoverflow.com/questions/2...ivot-examples]

select
distinct a,
(select distinct t2.b from t t2 where t1.a=t2.a and t2.b='Ring'),
(select distinct t2.b from t t2 where t1.a=t2.a and t2.b='Sword')
from t t1
Azerath is offline   Reply With Quote
Old August 31, 2011, 16:59   #12
Timo Pietilä
Prophet
 
Join Date: Apr 2007
Location: Climbing up from hole I just dug.
Posts: 4,096
Timo Pietilä is on a distinguished road
Quote:
Originally Posted by Magnate View Post
SELECT level, pval, COUNT(*) FROM `wearables_pval_flags` WHERE (level BETWEEN 1 AND 100) AND (pval BETWEEN 1 AND 50) AND (of_idx IS 13) GROUP BY level, pval ORDER BY level, pval
I think your select is too restricted.

Try SELECT *, COUNT(*) FROM ....
Timo Pietilä is offline   Reply With Quote
Old August 31, 2011, 18:25   #13
NinjaN
Rookie
 
Join Date: Aug 2011
Location: Münster, Germany
Posts: 7
NinjaN is on a distinguished road
@Timo:

SELECT *, COUNT(*)... will not work, at least as far as I know. You can't group by * which is necessary for COUNT() to work. One could SELECT every field and GROUP BY every field but then COUNT() is almost certainly always 1 (because each unique set of fields exists once, eg. there is an autoincremented counter for id'ing that row).


@Magnate:

Your query

SELECT level, pval, COUNT(*)
FROM `wearables_pval_flags`
WHERE (level BETWEEN 1 AND 100)
AND (pval BETWEEN 1 AND 50)
AND (of_idx IS 13)
GROUP BY level, pval
ORDER BY level, pval

is correct in syntax. Since I don't know about the structure of the database I can't say if it is 'asking' for the correct data...


@Azerath:
You are correct, subqueries can make this result better readable. The query should work like this:

SELECT DISTINCT level,
(SELECT COUNT(*) AS '+1' FROM 'wearable_pval_flags' wpf2 WHERE (wpf2.level = wpf1.level) AND (pval = 1) AND (of_idx = 13)) AS 'Speed +1',
(SELECT COUNT(*) AS '+1' FROM 'wearable_pval_flags' wpf2 WHERE (wpf2.level = wpf1.level) AND (pval = 2) AND (of_idx = 13)) AS 'Speed +2',
(SELECT COUNT(*) AS '+1' FROM 'wearable_pval_flags' wpf2 WHERE (wpf2.level = wpf1.level) AND (pval = 3) AND (of_idx = 13)) AS 'Speed +3',
(SELECT COUNT(*) AS '+1' FROM 'wearable_pval_flags' wpf2 WHERE (wpf2.level = wpf1.level) AND (pval = 4) AND (of_idx = 13)) AS 'Speed +4',
(SELECT COUNT(*) AS '+1' FROM 'wearable_pval_flags' wpf2 WHERE (wpf2.level = wpf1.level) AND (pval >= 5) AND (of_idx = 13)) AS 'Speed > +4'
FROM 'wearable_pval_flafs' wpf1
WHERE (wpf1.level BETWEEN 1 AND 50)
ORDER BY level


@Magnate:
What the above query does is the following:

It asks for all unique entries for level (DISTINCT is the Keyword for that) in your table and reports them once only.

Notice the alias 'wpf1' in the FROM part. This alias can be used instead of the full table name. You see that wpf1 is my alias for the 'outer' table while wpf2 is used inside the subqueries. That way, we can address the same table twice while being able to query a specific 'copy' of that table.

In the following nested SELECT-statements (also called subqueries), the value for level is put in as filter (wpf2.level = wpf1.level) while the other filters are just for wpf2 (no alias always means 'this table' which is the inner one). Subqueries can only deliver exactly one field, COUNT(*) in this example.

Behind each subquery you see another alias. This is an alias for that column, so it gets a name in your result. You can also give 'normal' fields an alias, so instead of eg. 'pval' you get 'speed enhancement' in your result. It's important to know that you can't use this alias in other parts of your query. An alias for columns only shows in the final result set, nowhere else. Aliases for tables can be used in your whole query, though.


Hope this is as helpful and understandable as my other post...


Cheers, NinjaN
NinjaN is offline   Reply With Quote
Old September 1, 2011, 11:23   #14
Magnate
Angband Devteam member
 
Join Date: May 2007
Location: London, UK
Posts: 5,057
Magnate is on a distinguished road
Send a message via MSN to Magnate Send a message via Yahoo to Magnate
Thanks again - it's getting a little more complicated now so it might take me a while to get my head around the aliasing stuff, but I will definitely try something like this to get a table of results.
__________________
"3.4 is much better than 3.1, 3.2 or 3.3. It still is easier than 3.0.9, but it is more convenient to play without being ridiculously easy, so it is my new favorite of the versions." - Timo Pietila
Magnate is offline   Reply With Quote
Old September 2, 2011, 15:01   #15
Xaxyx
Scout
 
Join Date: Feb 2010
Posts: 35
Xaxyx is on a distinguished road
Pardon me for jumping in uninvited, but aren't we overcomplicating things a bit? Are we just looking for counts?

SELECT level,
SUM(CASE WHEN pval = 1 AND of_idx = 13 THEN 1 ELSE 0 END) AS 'Speed +1',
SUM(CASE WHEN pval = 2 AND of_idx = 13 THEN 1 ELSE 0 END) AS 'Speed +2',
SUM(CASE WHEN pval = 3 AND of_idx = 13 THEN 1 ELSE 0 END) AS 'Speed +3',
SUM(CASE WHEN pval = 4 AND of_idx = 13 THEN 1 ELSE 0 END) AS 'Speed +4',
SUM(CASE WHEN pval > 4 AND of_idx = 13 THEN 1 ELSE 0 END) AS 'Speed > +4'
FROM wearable_pval_flags
WHERE level BETWEEN 1 AND 50
GROUP BY level
ORDER BY level
Xaxyx is offline   Reply With Quote
Old September 5, 2011, 08:55   #16
NinjaN
Rookie
 
Join Date: Aug 2011
Location: Münster, Germany
Posts: 7
NinjaN is on a distinguished road
@Xaxyx:

You are not uninvited, at least not more than me... ;-P

Your query should work as well...
In fact, it should even work better than my solution, in other words: it will be faster (because there're no subqueries which need more time during execution). The results should be identical.

As I said, I'm no guru, I still learn something new every day... I will try to test your solution whe I get the chance...


Cheers, NinjaN
NinjaN is offline   Reply With Quote
Old September 5, 2011, 12:29   #17
Psi
Knight
 
Join Date: Apr 2007
Location: Surrey, UK
Age: 44
Posts: 870
Psi is on a distinguished road
I'm a DBA rather than a SQL developer, but for queries like that I go for the sum(case...) structure like Xaxyx has posted.
Psi is offline   Reply With Quote
Old September 5, 2011, 12:36   #18
Azerath
Rookie
 
Join Date: Jun 2011
Posts: 21
Azerath is on a distinguished road
Is the goal to have optimized queries, or just returning valid results?

This topic starts to drift to SQL optimization, not helping get "the results" ;-)
Azerath is offline   Reply With Quote
Old September 5, 2011, 12:53   #19
Psi
Knight
 
Join Date: Apr 2007
Location: Surrey, UK
Age: 44
Posts: 870
Psi is on a distinguished road
Quote:
Originally Posted by Azerath View Post
Is the goal to have optimized queries, or just returning valid results?

This topic starts to drift to SQL optimization, not helping get "the results" ;-)
Doesn't the query above do exactly what Magnate asked for in the original post? The fact it is clearer to read and quicker to run is surely a good thing...
Psi is offline   Reply With Quote
Old September 5, 2011, 17:05   #20
NinjaN
Rookie
 
Join Date: Aug 2011
Location: Münster, Germany
Posts: 7
NinjaN is on a distinguished road
Quote:
Originally Posted by Azerath View Post
Is the goal to have optimized queries, or just returning valid results?

This topic starts to drift to SQL optimization, not helping get "the results" ;-)
I think the goal is a good mix of both! You can have valid results which execute horribly slow and complicated, sure. But why not optimize your queries if you know how to do it?

I didn't realize CASE could be a good way to do it but it surely is. I've even incorporated that knowledge into my work (you know, the paid one) and impressed some collegues with it's elegance... ;-P


@Magnate:
As you can perhaps see here, may ways lead to Rome. Ask 3 people the exactly same question "how do I do this using SQL" and you are sure to get at least 4 different answers.

In the end it's up to you which way you want to go. In general, the most simple to read query is the one which executes fastest, at least to my experience. If it does what you want in a way you can understand and in a time you are willing to wait, then it's a good query!


Cheers, NinjaN
NinjaN is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 20:24.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.