It is all about the SQL

Reply #32

Sunglasses — Wed, 07 Jun 2017 00:24:50 GMT

Hm?

N1 N2 N3 N4 N5 N6 N7 N8 N9 N10 F10 F9 F8 F7 F6 F5 F4 F3 F2 F1 F0

1 2 3 4 5 6 7 8 9 10 0 0 2 8 63 224 571 817 702 358 66

1 2 3 4 5 6 7 8 9 11 0 0 2 11 73 243 507 839 708 358 70

1 2 3 4 5 6 7 8 9 12 0 0 0 11 67 242 527 831 732 325 76

1 2 3 4 5 6 7 8 9 13 0 0 2 14 67 256 534 785 725 357 71

1 2 3 4 5 6 7 8 9 14 0 0 2 14 63 231 537 828 711 355 70

1 2 3 4 5 6 7 8 9 15 0 0 0 14 69 244 532 790 752 333 77

1 2 3 4 5 6 7 8 10 11 0 0 2 7 74 222 544 817 725 352 68

1 2 3 4 5 6 7... [ More ]

Reply #31

Novan60 — Tue, 06 Jun 2017 00:05:58 GMT

9 out of what, 80? I would not advise that as that would be 232 billion combinations of numbers. You are better off using math calculate whatever results you are trying to obtain.

Reply #30

Sunglasses — Mon, 05 Jun 2017 22:05:04 GMT

Can you put all 9 number combinations in the DB

Reply #29

Novan60 — Thu, 01 Jun 2017 06:42:08 GMT

Normally I calculate combinations of number to determine their frequency which is much faster and able to handle much larger combinations of numbers. However if you are not comfortable with doing those kinds of calculations the following query can be used to determine number combinations and frequencies.

CAUTION: I would not use this query to calculate KENO combinations as it will take several days if not weeks to run. It took my computer 10 minutes to run all combinations of 6 digits for 59

Reply #28

Sunglasses — Sat, 20 May 2017 15:39:50 GMT

I don't mind learning with anything like a well written course and look up tools. Mostly you don't find this, online courses often suck or ar old. I didn't have the cash to buy r-pie. So, I didn't start with it. Any complete online course with pdf and more? Probably not.

Reply #27

RL-RANDOMLOGIC — Thu, 18 May 2017 02:55:25 GMT

Lexicographical index example code is all over the net these days, 25 years ago, not so much.

My code will calculate any index the problem is related to MS. After all, who would attempt to

play a pick-30 of 2000 unless it paid for matching none of the numbers?

Python has really moved up in the arena of programming languages in the last few years. I

experimented with it years back but disliked the process required to build stand alone apps.

Everything has it's trade offs.

RL... [ More ]

Reply #26

Sunglasses — Tue, 16 May 2017 22:22:27 GMT

Trainings cost a fortune. I know some of my sqlite and basic Oracle SQL. Little TSQL, I have to look up everything on Google, missing a good textbook or r pertoire.

Reply #25

Sunglasses — Tue, 16 May 2017 22:08:05 GMT

2000 numbers, 30 drawn

INDEX N1 N2 N3 N4 N5 N6 N7 N8 N9 N10 N11 N12 N13 N14 N15 N16 N17 N18 N19 N20 N21 N22 N23 N24 N25 N26 N27 N28 N29 N30

2073321621560102598136661599193831378395614538174141857111978452369 67 68 74 135 283 416 430 616 629 633 641 651 729 779 964 993 1138 1185 1193 1231 1314 1373 1391 1460 1520 1593 1633 1649 1715 1902

268371350296939504838382476756800457108752088805813377575834812892 6 85 160 228 278 315 382 398 429 466 664 727 753 785 813 837 913 923 1012 1156 1183 1... [ More ]

Reply #24

Sunglasses — Tue, 16 May 2017 22:06:31 GMT

Lotto with 200 numbers, 20 drawn:

INDEX N1 N2 N3 N4 N5 N6 N7 N8 N9 N10 N11 N12 N13 N14 N15 N16 N17 N18 N19 N20

1095118256966979374233657019 11 18 31 51 61 70 72 81 85 87 95 96 97 119 124 128 134 149 158 187

240791765486715066673814501 2 10 21 31 34 38 42 46 60 68 73 82 89 102 112 116 132 137 150 159

804749010016394572697893580 7 14 15 45 51 52 59 61 64 69 79 81 90 105 134 135 145 154 195 199

663797764867806662409022660 5 39 58 61 63 69 74 81 96 106 116 124 135 139 153 155 180 188

Reply #23

RL-RANDOMLOGIC — Tue, 16 May 2017 09:25:18 GMT

I ran into the same type problems when factoring large numbers, I was trying to generate the lexicographical index for

a pick 20 of 80 matrix but my code can't handle anything over a pick 15 of 80.

Pick-20 of 80 = 3,535,316,142,212,174,320 - total sets 19 digits

Pick-15 of 80 = 6,635,869,816,740,560 - total sets 16 digits

15 of 80 matrix sample lexicographical index

RL

Reply #22

Novan60 — Tue, 02 May 2017 20:29:50 GMT

Supposed I wanted to play a strategy where I was going to play the same number until it had hit in three drawings. Normally I would have handled that by writing some VB code to figure out the sequence number in real time or created a table in SQL that keeps a running total. Now I have a third option.

I created a new function that leverages the BaseN function from earlier to calculate the frequency.

Here is the code:

IF OBJECT_ID (N'dbo.UT_FN_Segment', N'FN') IS NOT NULL... [ More ]

Reply #21

Novan60 — Sun, 23 Apr 2017 19:39:36 GMT

Thanks for the compliment, although I should probably do more to document them.

I haven't used Access for this stuff in about 10 years as it was too limiting, but it was a great starter tool for my initial dive.

I only use Excel to sometimes store results from historical analysis done in VB that I don't want to save in my database. While I get why some people use it to do a lot of analysis and strategy formulation it is also hard for me to understand why they would continue to constrain t... [ More ]

Reply #20

Datapile — Sun, 23 Apr 2017 17:47:40 GMT

Be warned Novan- Excel and Access both truncate any thing over 15 digits! If you are doing large number calculations that go to 16+ digits, MS products will just start making the last digits 0. This applies to both 32 and 64 bit office apps. I started to run into this, and like the others above moved to python and sqlite- they will handle any number no matter the size.

P.S. IMHO, your SQL statements are works of art.

Reply #19

Novan60 — Fri, 14 Apr 2017 17:28:31 GMT

I sometimes play a lot of games over several days and it can be frustrating to figure out which slips have the winners especially if they get out of order. By using the GAME_FN_Results function posted earlier the following query can be written which pulls all of the winning pay slips and the first set of numbers on the slip making it easy to identify the winning slips.

Select s.StartDrwing , s.EndDrwing , s.GameCard , TotalCash , PickedNums from vwGAmes as G inner join

Reply #18

Novan60 — Wed, 05 Apr 2017 23:04:17 GMT

One of the benefits of storing the winning number in a table like tblWinDigits where the only essential columns are the Drawing, position and number is that it makes it really easy to look at winning results.

I use it like this on the page that I use to collect the numbers from the lotto web site. After it has harvested the number it automatically refreshes the results with whatever I played that drawing.

It also works for Lotto with the bonus number. It does not work for MM or PB,... [ More ]

Reply #17

Novan60 — Wed, 05 Apr 2017 22:25:39 GMT

While I found getting the details on how many times a number appeared with other numbers useful, it was too much information to visually process so I came up with another way to display the same data that also runs much faster.

The first row in the MatchHist column represents the combined history of the select number in the last 20 drawings where any one or more of them appeared. The NumHist column represents the performance of that number in the last 4 drawings.

Here is the SQL that I... [ More ]

Reply #16

lucky1day — Sat, 01 Apr 2017 04:51:30 GMT

For clients I use SQL, for my lottery filtering strategies, I use the same as you python 3 and sqlite. You can't beat that combination for quick and dirty code solutions.

Reply #15

Quench — Sat, 01 Apr 2017 03:20:50 GMT

Thank you for your post . I use python and sqlite (an in memory version of SQL ) for back testing and prediction filtering on a linux system. SQL is portable over most computer platforms and the time spent learning its ins and outs are well spent. Once the rules are laid out it's a great automation tool.

Reply #14

Novan60 — Thu, 30 Mar 2017 06:26:04 GMT

One of the strengths of a SQL database is that it opens the door to being able to look at large chunks of date in a wide variety of ways. In the sample of data posted below what I did was take the 20 winning numbers from 3,000 drawings, paired them up with each other and looked at what their totals were compared to the other available numbers in a given drawing. It took about 4 hours for two computers to process the data.

The statistic that I chose to look at was lifetime hits. Because lifeti... [ More ]

Reply #13

Novan60 — Thu, 30 Mar 2017 05:43:41 GMT

With some additional work a function could be written to show what the statistics are for the numbers that have already been picked and what combinations the remaining numbers would produce if they were picked.

For analysis purposes I included a flag to indicate whether or not the number hit in the current drawing. In looking at this I this data I can see that the combination that had gone the longest without hitting hits in the current drawing.

Reply #12

Novan60 — Thu, 30 Mar 2017 05:29:51 GMT

By combining the Odd/Even Functions along with some routines to pull last hit, in a row and hits in X drawings makes it possible to get detailed information on numbers going into the next drawing with a simple query.

Here is the SQL to build this function:

IF OBJECT_ID ('dbo.HIST_FN_N0Stats', N'TF') IS NOT NULL

DROP FUNCTION dbo.HIST_FN_N0Stats;

go

Create Function dbo.HIST_FN_N0Stats(@Drwing int ,@IncludeNums Varchar(500), @ExcludeNums VarChar(500))

return... [ More ]

Reply #11

Novan60 — Thu, 30 Mar 2017 03:53:00 GMT

SQL has something called functions that can be used to handle calculations and operations that are repeated. The following is an example of functions in use. One function determines whether a number is odd or even and then other does what I refer to as basing (although I'm sure there is some proper mathematical term for it).

Here is the code for those functions:

IF OBJECT_ID (N'dbo.UT_FN_OddEven', N'FN') IS NOT NULL

DROP FUNCTION dbo.UT_FN_OddEven;

GO

CREATE FUNCTION... [ More ]

Reply #10

Novan60 — Fri, 24 Mar 2017 06:04:21 GMT

Made some changes.

Added performance in the last 7,14,21,28 and 100 drawings.

Added the Num columns which track the individual number history.

I normally run the script from within my code, but made it so that it can be run from a query window without having to edit the query

declare

@Drwing varchar(20) = 1000,

@Nums varchar(500) = '1,5',

@SQL varchar(MAx),

@NumCount varchar(20)

SELECT @NumCount = LEN(@nums) - LEN(REPLACE(@Nums, ',', '')) + 1... [ More ]

Reply #9

Novan60 — Wed, 22 Mar 2017 06:32:58 GMT

One reason to store historical drawing data by digit instead of a single entry is that it opens the door to collect some summary data prior to bringing it into the application or spreadsheet. In the following query I have already chosen 1 and 20 and am interested in seeing how the remaining numbers have performed with those numbers.

Select N0, Max(Drwing) as LastDrwing, Min(DrwingSeq )-1 as SEP, Max(IAR) as IAR, COUNT_BIG (*) as HPP from

(Select n0, Drwing , DrwingSeq , SeqNo , iif(drwings... [ More ]

Reply #8

Novan60 — Wed, 22 Mar 2017 06:18:11 GMT

This format is the primary way that I store my historical drawing data as it makes accessing the winning numbers from a drawing quick and easy.

However there are times when I want to crunch some data prior to bringing it into my application so I also store drawing data in the following format. Here are samples from NY Lotto and Pick 10.

With games like Lotto and MM that have additional numbers I use the value of 99 in the position field to separate it from the other numbers.

Reply #7

Sarge0202 — Wed, 22 Mar 2017 03:25:27 GMT

Well databases have 2 purposes with lotteries.

Finding patterns: Examples are min/max on the sum of a set of numbers, frequencies of numbers picked, frequencies of numbers picked in a date range, frequencies of numbers in ranges (1-10,11-20, etc), frequencies of number sequences and length, etc

Testing picking theories: Say, for example, you choose (poorly) the last set of numbers for the next game. With a program, you can have it loop thru all the games on file and seeing how many matches... [ More ]

Reply #6

lucky1day — Thu, 16 Mar 2017 19:05:46 GMT

And regarding C#, more often than not the data being processed by whatever application is written is going to use SQL to access the data and poorly written SQL and data structures will result in poor performance. Although I have to say that views like yours have allowed me to earn a good living off helping businesses find work around for poorly designed applications that do a lousy job of accessing data.

I absolutely love it, because I've made tons of money for the same reason. I'm finding t... [ More ]

Reply #5

Sunglasses — Thu, 16 Mar 2017 09:25:37 GMT

Show me your rank 1, 2 and 3 winning tickets.

Reply #4

Novan60 — Thu, 16 Mar 2017 02:50:20 GMT

And this is a perfect example of why the web can be a dangerous place to pick up knowledge. You have no idea of what you are talking about yet are attempting to speak authoritatively as if you know what you are talking about.

First off you are comparing apples to oranges. Excel is predominantly a presentation layer tool, not a data storage tool. The strength or Excel is that it offers end users the tools to parse data that has already been cut up into bite sized chunks from a data layer.

S... [ More ]

Reply #3

Sunglasses — Thu, 16 Mar 2017 00:05:22 GMT

You could have shown a simple Select query. You had to pull out that cross query. And it is slower than Excel in processing data. You're stuck without C# or other.

Reply #2

Novan60 — Wed, 15 Mar 2017 20:52:24 GMT

SQL is not unique to Microsoft. There are multiple free SQL products out there like MYSQL that use the same basic SQL syntax. I mentioned SQL Server from Microsoft because the free developer version is more robust than any of the other free SQL products out there.

I did not create this thread as a step by step guide on how to set up a SQL server and write a query. People can get SQL for Dummies or some other entry level book to learn the basics.

In my last job I used to regularly meet peop... [ More ]

Reply #1

Sunglasses — Wed, 15 Mar 2017 19:36:24 GMT

Just a pdf or link to course does it. If you have to post all questions you can figure out on microsoft products your stuck here until the end of time. Beyond you start with installation of products before writing TSQL. Add that the execution can be painfully slow.

It is all about the SQL

Novan60 — Tue, 14 Mar 2017 00:13:11 GMT

SQL can be a very important tool to help determine and test strategies.My own preference is to use a combination of SQL and VB. But even if you want to stick to using Excel for analysis, storing the data in a SQL database can add a wide range of options.

SQL Server can be downloaded for free from Microsoft if you don't already have access to it.

SQL can be quite complex, but at it's core the structure is fairly straightforward. Data is queried use a select statement that might look somethi... [ More ]