Re: Basic SELECT help
Hi Neil,
On 11/22/2012 7:14 PM, h...@tbbs.net wrote:
2012/11/22 14:30 +, Neil Tompkins
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
This ugly one, which generalizes:
select id,group_concat(type) AS tl from the_table group by id having
find_in_set('2',tl) and find_in_set('5',tl)
Ugly becaus it involves so much converting between number & string.
For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my "tl" and a string, say '1,2', to a procedure that using
SUBSTRING_INDEX taking the strings for arrays ensures that all found in the first string
is also in the second string. There are times when I wish SQL had arrays.
The fun part of solving this is to remember that SQL is a set-oriented
language. For each element in the set, none of them can be both 2 and 5
at the same time. So, you have to build two sets and check to see which
rows are in both.
One pattern works if you need to aggregate for just a few terms
SELECT a.id
from (select distinct id from mytable where type=2) a
INNER JOIN (select distinct id from mytable where type=5) b
on a.id=b.id
However, this gets numerically very expensive with more than a few JOINS
to the pattern. Also, there is no index on either of the temporary
results (a or b) so this is a full Cartesian product of both tables.
That means that although it gives you a correct answer, it will not
scale to 10's of rows (or more) in either set.
So, here is a way to assemble the same result that uses much less
resources. Remember, each row you want is a member of a set.
CREATE TEMPORARY TABLE tmpList (
id int
, type int
, PRIMARY KEY (id,type)
)
INSERT IGNORE tmpList
SELECT id,type
FROM mytable
WHERE type in (2,5)
SELECT id, count(type) hits
FROM tmplist
GROUP BY id
HAVING hits=2
DROP TEMPORARY TABLE tmpList
Can you see why this works?
I created an indexed subset of rows that match either value (2 or 5) but
only keep one example of each. I accomplished that by the combination of
PRIMARY KEY and INSERT IGNORE. Then I counted many type values each ID
value represented in the subset. If I looked for 2 terms and I ended up
with hits=2, then I know that those ID values matched on both terms.
You can expand on this pattern to also do partial (M of N search terms)
or best-fit determinations.
I hope this was the kind of help you were looking for.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: blank line when column changes
Yeah, it's easy enough to do in python, but my client wanted a SQL query. Thanks On Tue, Dec 18, 2012 at 5:48 PM, Rick James wrote: > GROUP BY .. WITH ROLLUP > could get you something like that. > > Otherwise, I would advise that you use your application language (PHP, Java, > etc) to do "presentation" type stuff. > > Yes, it could be done is SQL, but my brain might explode if I try to conjure > up such. > >> -Original Message- >> From: Larry Martell [mailto:larry.mart...@gmail.com] >> Sent: Monday, December 17, 2012 5:18 AM >> To: mysql mailing list >> Subject: blank line when column changes >> >> I have this query: >> >> SELECT q1.t, data_eventlog.message, q1.dt, data_eventlog.date_time FROM >> (SELECT cdsem_event_message_idx.message m, >> cdsem_event_message_idx.date_time dt, >> data_tool.name t, >> cdsem_event_message_idx.tool_id tid >> FROM data_tool, cdsem_event_message_idx >> WHERE data_tool.id = cdsem_event_message_idx.tool_id >> AND cdsem_event_message_idx.message_idx = 'ExitingToOn') q1, >> data_eventlog WHERE data_eventlog.date_time <= q1.dt AND >> TIMESTAMPDIFF(SECOND, data_eventlog.date_time, q1.dt) < 120 AND >> data_eventlog.tool_id = q1.tid ORDER BY q1.t, q1.dt, >> data_eventlog.date_time >> >> What I want to do is output a blank line whenever (q1.t, q1.dt) >> changes. Is there any way to do that in SQL? >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: blank line when column changes
GROUP BY .. WITH ROLLUP could get you something like that. Otherwise, I would advise that you use your application language (PHP, Java, etc) to do "presentation" type stuff. Yes, it could be done is SQL, but my brain might explode if I try to conjure up such. > -Original Message- > From: Larry Martell [mailto:larry.mart...@gmail.com] > Sent: Monday, December 17, 2012 5:18 AM > To: mysql mailing list > Subject: blank line when column changes > > I have this query: > > SELECT q1.t, data_eventlog.message, q1.dt, data_eventlog.date_time FROM > (SELECT cdsem_event_message_idx.message m, > cdsem_event_message_idx.date_time dt, > data_tool.name t, > cdsem_event_message_idx.tool_id tid > FROM data_tool, cdsem_event_message_idx > WHERE data_tool.id = cdsem_event_message_idx.tool_id > AND cdsem_event_message_idx.message_idx = 'ExitingToOn') q1, > data_eventlog WHERE data_eventlog.date_time <= q1.dt AND > TIMESTAMPDIFF(SECOND, data_eventlog.date_time, q1.dt) < 120 AND > data_eventlog.tool_id = q1.tid ORDER BY q1.t, q1.dt, > data_eventlog.date_time > > What I want to do is output a blank line whenever (q1.t, q1.dt) > changes. Is there any way to do that in SQL? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: sales data every publisher
On 12/18/2012 3:52 AM, Haidar Pesebe wrote: Hi all-- There are 3 tables which each like this: NOTE: The linkage between table A and table B is ID and IDPUBLISHER, while Table B to C is the ISBN. Sometimes there are some titles that are not sold in a given month. TABLE A (Publisher) --- ID : NAME : EMAIL : --- 1 : ABC : abc@abc 2 : CDE : cde@cde --- TABLE B (BOOKS TABLE) : IDBOOK: TITLE : PUBLISHER ID : ISBN : 1 :TITLE 01 : 1 : 001 : 2 :TITLE 02 : 1 : 002 : 3 :TITLE 03 : 2 : 003 : 4 :TITLE 04 : 2 :004 TABLE C (SALES OF BOOKS) -- : IDSALES : ISBN : PRICE : QTY : DATE : --- : 1: 001: 100: 20 : 2012-12-01 : : 2: 001: 100 : 11 : 2012-12-01 : : 3: 002: 60 : 15 : 2012-12-01 : : 4: 003: 30: 10 : 2012-12-01 : : 5: 003: 30 : 7 :2012-12-01 : : 6: 003: 30: 8 :2012-12-01 : : 7: 004: 50 : 10 : 2012-12-01 : --- How do I call up the sales in December 2012 for ABC Publisher or call the sale in 2012 for ABC publisher? RESULT OF Sales Books of ABC Publisher in December 2012 --- No. : Books Title : ISBN :QTY : AMOUNT --- 1. : Title 01 : 001 : 31 : 3,100 2. : Tile 02 : 002 : 15 : 900 and so on . --- help me to solve this problem 1) You need some joins. This is how you link your rows together. For data that can be there but isn't required to be there, you use one of the OUTER JOIN terms of LEFT JOIN or RIGHT JOIN. This allows us to combine columns from different tables into the same report. This is also a good time to learn about using aliases for table names and column names 2) You need a GROUP BY to summarize certain values (like amount) for the rows you retrieve. 3) You use some conditions in a WHERE clause to limit what it is you want to summarize. SELECT p.name # the name of the publisher , b.title as 'Books Title' , b.ISBN , SUM(s.QTY) as 'QTY' # the number actual books sold , SUM(s.QTY * s.PRICE) as 'AMOUNT' # the total value of all books sold FROM publishers p INNER JOIN books b ON b.`publisher id` = p.id # this is optional information as a book may not have any sales data for the given date range so we use a LEFT JOIN LEFT JOIN sales s ON s.ISBN = b.ISBN WHERE s.date >= '2012-12-01' and s.date < '2013-01-01' GROUP BY p.name, b.title, b.ISBN For more details: http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html http://dev.mysql.com/doc/refman/5.5/en/examples.html http://dev.mysql.com/doc/refman/5.5/en/select.html http://dev.mysql.com/doc/refman/5.5/en/join.html And, as always, you can ask the list. Best wishes, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: sales data every publisher
On 2012-12-18 2:52 AM, Haidar Pesebe wrote: Hi all-- There are 3 tables which each like this: NOTE: The linkage between table A and table B is ID and IDPUBLISHER, while Table B to C is the ISBN. Sometimes there are some titles that are not sold in a given month. TABLE A (Publisher) --- ID : NAME : EMAIL : --- 1 : ABC : abc@abc 2 : CDE : cde@cde --- TABLE B (BOOKS TABLE) : IDBOOK: TITLE : PUBLISHER ID : ISBN : 1 :TITLE 01 : 1 : 001 : 2 :TITLE 02 : 1 : 002 : 3 :TITLE 03 : 2 : 003 : 4 :TITLE 04 : 2 :004 TABLE C (SALES OF BOOKS) -- : IDSALES : ISBN : PRICE : QTY : DATE : --- : 1: 001: 100: 20 : 2012-12-01 : : 2: 001: 100 : 11 : 2012-12-01 : : 3: 002: 60 : 15 : 2012-12-01 : : 4: 003: 30: 10 : 2012-12-01 : : 5: 003: 30 : 7 :2012-12-01 : : 6: 003: 30: 8 :2012-12-01 : : 7: 004: 50 : 10 : 2012-12-01 : --- How do I call up the sales in December 2012 for ABC Publisher or call the sale in 2012 for ABC publisher? select b.idbook, b.title, b.isbn, s.qty, sum(s.qty) as qty, Sum(s.qty * s.price) as amount from sales s join books b on s.isbn = b.isbn join publisher p on b.publisherID=p.id group by b.idbook, b.title, b.isbn; PB RESULT OF Sales Books of ABC Publisher in December 2012 --- No. : Books Title : ISBN :QTY : AMOUNT --- 1. : Title 01 : 001 : 31 : 3,100 2. : Tile 02 : 002 : 15 : 900 and so on . --- help me to solve this problem Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL Cluster alerts
Hi Bheemsen, looks like a few different things going on there; if you have a MySQL support contract/subscription then it would be worth raising SRs - it doesn't need to be a bug, it's fine tyo ask questions too. A couple of things that spring to mind in-line > I am frequently seeing the following alerts in our production MySQL Cluster > environment. Do you have any metrics, guidelines and scripts to monitor and > fix these alerts? Any help is appreciated. > > Temporary Tables To Disk Ratio Excessive > Excessive Disk Temporary Table Usage Detected > > Table Scans Excessive > Indexes Not Being Used Efficiently If you're using MySQL Cluster 7.2 then you should run OPTIMIZE TABLE for each of your tables (repeat that step whenever you make schemas changes to it, add an index or make very signifficant data changes). This will make the optimizer make better use of available indexes. Use the query analyzer in MySQL Enterprise Monitor (MEM) to see which queries are taking the time as these are likely to be the table scans (full table scans should be avoided as much as possible). You can use the EXPLAIN command to see if individual queries are making use of the available indexes. Try adding new indexes if they're missing for high-running transactions. > > Thread Cache Size May Not Be Optimal > > Cluster DiskPageBuffer Hit Ratio Is Low Note that you might observe this after restarting a data node as the cache must be repopulated as queries come in. If you're seeing this at other times or the MEM graphs show that the DiskPageBuffer Hit Ratio is consistently low then consider increasing it... http://dev.mysql.com/doc/mysql-cluster-excerpt/5.1/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-diskpagebuffermemory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
