Advance usage of the SELECT keyword
Table1 includes only three song bouts produced by a bird named b109. In our database
you will find () a table called b109clustered, which contains the entire song development
of this bird. In this table you may not want to run the query shown above, because this
query would return hundreds of thousands of records.
In this case, you should always using the keyword limit, e.g., (Matlab):
[c1_duration, c1_pitch]=mysql('select duration, mean_pitch from b109clustered
where duration>100 and duration<150 and mean_pitch>600 and mean_pitch<1100
limit 5000;');
will execute promptly and will return the first 5000 syllables that match your criteria.
Because this table is already “clustered”, namely, the syllable types are already identified
(see chapter xx) you could have simplified this query since this cluster is already
identified as cluster number 4, so this query can turn to
select duration, mean_pitch from b109clustered where cluster=4 limit 5000;
Now, this query will return the first 5000 syllables of type 4, but what if you want not the
first 5000 syllables by a random sample of 5000 syllables? That's easy:
select duration, mean_pitch from b109clustered where cluster=4 order by rand()
limit 5000;
What if you only want to count how many syllables of each count exists in the table?
select count(*) from b109clustered where cluster=4;
will return 173581, so you can now repeat this query for clusters 0 (not clustered), 1
(introductory notes) and 3, 4 (song syllable types of this bird) and tell that in this bird
the frequency of types over development is:
0: 279354
1: 337884
3: 198997
4: 173581
We can now see if those types are present in similar proportion throughout development.
To do so, we will limit our queries to different dates.
select count(*) from b109clustered where month=8 and day<19 and cluster=4;
will return 0, but
select count(*) from b109clustered where month=8 and day=20 and cluster=4;
will return 858, telling us that (using our clustering method) this cluster was not
identified before August 19.
It should be now quite easy for you to realize how to select data by any combination of
criteria. Using a simple for loop, you can pump the results of these queries into Matlab
functions. When properly used, the MySQL server is a very potent device, capable of
amazing performance returning complicated queries in a remarkable speed.
Created using Helpmatic Pro HTML