SQL Query Help

[thelearner]

With whatever method you chose create a non clustered index on horse_id, racedate

I presume you solved it all but if you need it running faster, paste your final code here, I spent over 10 years optimising sql server it's just what I do

 

[rpjd99]

I will have need of your skills one day thelearner , of that I'm certain.

On a side note, do you have any experience with database viewers? I use QlikView and find it excellent, but I'm still interested in the experience others have with other viewers.

Ray

 

[thelearner]

Sql server express(totally free) and then paste the results into a spreadsheet, or you after something more specific Ray

 

[rpjd99]

or you after something more specific Ray

Yeah, I was talking about SiSense, Tableau, etc. They import data from the DB and you can create visual dashboards with the data. I'm using SQL Server 2012 (Developer Edition - about £50) and I import specific data I require into both Excel (using VBA) and QlikView (using a QV script).

Ray

 

[Horseplayer]

Hi rpjd99 I don't use a viewer (although the speedfig pages are technically one)

Had a quick look at it, looked interesting but watching one of the videos looks like it's just using a lot of GROUP BY's.
I get the impression it's a little "Apple like", all shiny on the outside with nothing special inside.

It's basically
DB <----> QlikView ----> jQuery
where QlikView is an SQL generator

I am planning on writing something similar with Python as the mid layer, gives you a lot more flexibility and control, you could even throw your data through a neural net for eg.

 

[thelearner]

I'm too old fashion simply use group by and order by and view the patterns as numbers with a bit of sum avg std, so for instance

to find the average odds of winners at different lengths

select count(*),avg(odds), yards/100
from  cv_frhorse
where  final_position = 1
group by  yards/100
order by   yards/100

 

[thelearner]

Do you use set up your neural networks with each node as a variable or a single bit of a variable/field?

 

[Horseplayer]

Do you use set up your neural networks with each node as a variable or a single bit of a variable/field?

I've not set up any yet. The set up will depend on what I learn during/after "training" them. It may well be they'll offer nothing useful

 

[thelearner]

I've not set up any yet. The set up will depend on what I learn during/after "training" them. It may well be they'll offer nothing useful

Be fascinated to here how you get on with number of fields and the datarowset size and how long it takes to process.

If you can normalise the data so most variables are between 0 and 1 it may speed things up a bit.

 

[Horseplayer]

It's a little way down the road just yet thelearner

I'm actually in the process of un-normalising data, although you could call them pre calculated fields

 

[rpjd99]

Had a quick look at it, looked interesting but watching one of the videos looks like it's just using a lot of GROUP BY's.
I get the impression it's a little "Apple like", all shiny on the outside with nothing special inside.

It's basically
DB <----> QlikView ----> jQuery
where QlikView is an SQL generator

Hi Horseplayer ,

Ah no, you're being unfair to QlikView, although I do agree with your dismissal of Apple - post-Jobs! As it happens, there isn't a single GROUP BY/ORDER BY statement in my QV import script. They aren't necessary because QV imports the full contents of whatever columns you name from whatever SQL Server tables as separate tables in QV. So whenever I click on a particular element, all tables are reduced to show only every other element connected to the original element. Added to this is that you can expand each table with formulas that will give you, inter alia, A/E, Chi, etc. It's probably best to give you picture examples and you will see that it's not dissimilar to what you have on your site. Have always been very impressed with that - pity my interest lies in French racing!

So, let's take Paul Cole, who has a good deal of success over here. Here's the top of my QV trainers' table with results starting from 1982 to the present day:

Some columns need explanation: 'LastYr' is the last year the trainer has appeared in the DB; 'Horses' are the number of distinct horses trained that have run in PMU races (everyone can bet in these); 'Hall' are the number of distinct horses trained that have run in both PMU and PMH races (you have to be at the course to bet in the latter); 'Freq' is the average number of races run by all the trainer's horses; R/W/W%/P/P%-all are runs/wins/win%/places/place% for all horses trained; and 'IV' is the impact value for the trainer - the only measure I could give, as there are no published dividends at PMH meetings, except for the winner. Coloured cells are set up by formula as well.

Okay, I select Paul Cole (trainer) from the search box:

and I get:

There are lots of other things I can see instantly, for example the results for each year:

The class of race (Rec-* = claiming races):

Age and sex of the horses:


To be continued...

 

[rpjd99]

...Courses:

and a multitude of other elements, limited only by my imagination in setting them up. And all this from just clicking on P.F. Cole.

So, let's look only at the last five seasons plus the current season:
Select 2010 to 2015 (click 2010 and drag to 2015) in any of the year boxes scattered throughout the document:
Overall record 2010-2015:

Years:

Class (compare group and listed results with those above plus claiming races have become the norm since 2010):

Age and sex:

Courses:

Jockeys:

PMU SP rank:

And these are just the basic tables. I can then click on any one of the elements showing and it breaks that choice down across all other tables.

That'll do for now.

Ray

 

[Horseplayer]

Hi Ray

You don't need to use the Group by/order by in your scripts because QlikView is doing that for you.

It looks impressive and useful if your not prepared to write your own middle layer, but all I see in the above "tables" are a lot of GROUP BY reports with some stats added

 

[rpjd99]

Hi Horseplayer ,

I guess all stats, pivot tables, charts, etc. are by nature GROUP BY reports and I'm thankful that QV is doing it for me! Discounting all the setting up I had to do to get it to this stage (and it was considerable), it's perhaps worth bearing in mind that producing this necessitated typing in "P.F. Cole and clicking and dragging across six year values. That was it. I'd have had to write a lot of T-SQL code just to get a vanilla version of the above basic tables separately, not all together as in QV. It also has the added benefit of showing me visually any errors that have crept into the DB data, assuming I'm looking at the right table of course. This was invaluable to me when I first started to put the DB together - missing data, typos, nonsense data, etc.

I take it that by "middle layer" you mean an application like QV. I'd love to be in a position to contemplate doing something like that because it would mean that my days of wrestling with Window Functions and the endless trial-and-error exercises involved were well and truly over!

Ray

 

[thelearner]

As long as the tool does what you want it to do, it really doesn't matter unless it takes a very long time to run, as computers get faster, coders seem to write less optimised code