Post by Ipposnif on Dec 24, 2020 12:43:12 GMT
In this article, I will explain the basics of SQL, focusing on the aspects that are relevant for Ipposnif.
Before reading next, it is better to know how Ipposnif strategies work.
SQL (Structured Query Language) is a programming language created to manage databases but it can be used also to define mathematical expressions.
The simplest SQL expression can be a number, used to specify for example the size or the price of a bet.
This is an integer number
This is a decimal number and the decimal separator in SQL is always the dot (.)
Numbers can be negative
The comma used to group the digits for currency values is not allowed in SQL, and the money amount below should be written as 10500.20
More complex SQL expressions can refer to variables dedicated to the exchange betting. Ipposnif variables contain information about the markets, the runners, and more.
For example, the following expression returns 1/10 of the current capital of the strategy checked at that moment.
@capital is a variable and variables are always preceded by the symbol @
All the Ipposnif variables are read mode only: they can be read from a strategy, but they cannot be overwritten. Ipposnif takes care of keeping them up to date.
An SQL assertion is an SQL expression that can be true or false. The following SQL assertion could be used to define a bet trigger condition (true=place bet, false=do not place bet) and it means: bet only on runners with a current best BACK price greater than 10.50
If you want to join more conditions together you can use the AND/OR logic operators. AND means that all the terms must be true, OR means that at least one term must be true.
In the following example, we want to bet only runners that have a price greater than 10.50 and have been born in 2018 and later.
Parentheses (brackets) can be used in complex assertions to specify the priority of the AND/OR operators, deeper are the parentheses, the greater is the priority.
In the example below, the SQL engine will elaborate first the assertion within the parentheses, and then the remaining part: bet only runners that have a price greater than 10.50 AND have been born in 2018 OR (regardless of the value of the first part of the expression) the runners that have been manually selected by the user.
@best_back_price is a variable that returns a decimal value, @yob (year of birth) returns an integer value, @is_horse_selected returns a boolean value (0=false 1=true).
SQL used by Ipposnif is case insensitive, which means there is no difference if you write the expressions lower case, upper case, or a mix of them. The following three SQL assertions have the same result, but good practice is to write everything in lower case except the operators in upper case.
@horse_name is a variable that returns a string value. When we want to compare a string variable we need to surround the comparison string with apostrophes.
@horse_name='crianza'
@horse_NAME='CRIANZA'
@horse_Name='CrianZa'
When working with string variables we can use the LIKE operator and the % wildcard. For example, if we want to select for betting all the horses that have the world 'lead' in any part of their notes, the SQL assertion is:
If we want to bet horses that have won the last race, we have to select horses that have the character 1 on the right side of the @form string variable:
Another useful operator, that can be used with numeric and string variables is the IN operator. With it, you can specify multiple values in the comparison. The assertion is true if the value of the variable is present in the list.
@stall_number IN (1,2,3,11,12,13)
@country_code IN ('GB','IE')
In SQL, the numeric comparison operators (=, <, >, <=, >=, <>) don’t need to be separated by a space, instead, all the other operators (AND/OR/LIKE/IN/...) need to be separated by a space or a carriage return. To make more readable an SQL expression, you can add as many spaces and carriage returns as you want. The following two SQL assertions produce the same result.
@best_back_price>10 AND @yob>=2018
@best_back_price > 10
AND
@yob >= 2018
There is a group of variables that are especially important in Ipposnif: the index variables.
Every time Ipposnif checks the result of an SQL expression or SQL assertion, it provides the IDs (unique identifiers) of the strategy, market, and runner that it is checking at that moment. The image below is a visual explanation of this concept.
@id_strategy contains the ID of the strategy checked at that moment.
@id_race contains the ID of the market checked at that moment.
@id_horse contains the ID of the selection (runner/team/player/etc..) checked at that moment.
Using these variables as parameters passed to SQL functions, we can access any information we need in our strategies: a horse/race/strategy property, the size of the next bet in a betting progression, how much liquidity there is in a market, and much more.
So, in addition to the SQL variable, we have another important SQL entity: the SQL function.
An SQL function is a piece of SQL code that accepts one or more values (or variables) as input, processes these values, and returns the calculated value as output.
For example, if we want to know the official rating of the runner checked at that moment we can use the function dbo.fn_HorseProperty. This function expects two parameters in input: the ID of the runner and the name of the property we want to retrieve.
The ID of the runner is returned by @id_horse (one of the index variables explained above), and the name of the property is a specific string value, in this case, it is 'official_rating'.
dbo.fn_HorseProperty(@id_horse, 'official_rating')
The result of this function can be used in an SQL assertion. In the example below, we want to bet only horses that have an official rating greater than 100.
dbo.fn_HorseProperty(@id_horse, 'official_rating') > 100
The result of a function can be used also in the SQL expressions which define the size or the price of a bet. In the following example, if the runner official rating is lower than 100 we want to bet 2€, if it is greater we want to bet 5€.
CASE WHEN dbo.fn_HorseProperty(@id_horse, 'official_rating') < 100 THEN 2 ELSE 5 END
CASE is a powerful SQL command that allows us to add intelligence to our SQL expressions. It evaluates a condition and returns one of the multiple possible result expressions.
The syntax is:
CASE WHEN {condition to evaluate} THEN {value returned when condition is true} ELSE {value returned when condition is false} END
The condition to evaluate and the two possible results can be any SQL expression, and they can contain other nested CASE expressions.
From Ipposnif you can access Expression Watcher, a useful tool that allows you to watch in real-time the value of a variable or a function, and to debug your strategies.
From the Ipposnif strategies, you can use all the available Microsoft SQL Server functions, plus all the Ipposnif variables and functions dedicated to the exchange betting.
If a variable or a function you need is missing, you can read from here how to add your own.
Before reading next, it is better to know how Ipposnif strategies work.
SQL (Structured Query Language) is a programming language created to manage databases but it can be used also to define mathematical expressions.
The simplest SQL expression can be a number, used to specify for example the size or the price of a bet.
This is an integer number
8
This is a decimal number and the decimal separator in SQL is always the dot (.)
1.51
Numbers can be negative
-2
The comma used to group the digits for currency values is not allowed in SQL, and the money amount below should be written as 10500.20
10,500.20 (error)
More complex SQL expressions can refer to variables dedicated to the exchange betting. Ipposnif variables contain information about the markets, the runners, and more.
For example, the following expression returns 1/10 of the current capital of the strategy checked at that moment.
@capital/10
@capital is a variable and variables are always preceded by the symbol @
All the Ipposnif variables are read mode only: they can be read from a strategy, but they cannot be overwritten. Ipposnif takes care of keeping them up to date.
An SQL assertion is an SQL expression that can be true or false. The following SQL assertion could be used to define a bet trigger condition (true=place bet, false=do not place bet) and it means: bet only on runners with a current best BACK price greater than 10.50
@best_back_price > 10.50
If you want to join more conditions together you can use the AND/OR logic operators. AND means that all the terms must be true, OR means that at least one term must be true.
In the following example, we want to bet only runners that have a price greater than 10.50 and have been born in 2018 and later.
@best_back_price > 10.50 AND @yob >= 2018
Parentheses (brackets) can be used in complex assertions to specify the priority of the AND/OR operators, deeper are the parentheses, the greater is the priority.
In the example below, the SQL engine will elaborate first the assertion within the parentheses, and then the remaining part: bet only runners that have a price greater than 10.50 AND have been born in 2018 OR (regardless of the value of the first part of the expression) the runners that have been manually selected by the user.
@best_back_price is a variable that returns a decimal value, @yob (year of birth) returns an integer value, @is_horse_selected returns a boolean value (0=false 1=true).
(@best_back_price > 10.50 AND @yob = 2018) OR @is_horse_selected = 1
SQL used by Ipposnif is case insensitive, which means there is no difference if you write the expressions lower case, upper case, or a mix of them. The following three SQL assertions have the same result, but good practice is to write everything in lower case except the operators in upper case.
@horse_name is a variable that returns a string value. When we want to compare a string variable we need to surround the comparison string with apostrophes.
@horse_name='crianza'
@horse_NAME='CRIANZA'
@horse_Name='CrianZa'
When working with string variables we can use the LIKE operator and the % wildcard. For example, if we want to select for betting all the horses that have the world 'lead' in any part of their notes, the SQL assertion is:
@horse_note LIKE '%lead%'
If we want to bet horses that have won the last race, we have to select horses that have the character 1 on the right side of the @form string variable:
@form LIKE '%1'
Another useful operator, that can be used with numeric and string variables is the IN operator. With it, you can specify multiple values in the comparison. The assertion is true if the value of the variable is present in the list.
@stall_number IN (1,2,3,11,12,13)
@country_code IN ('GB','IE')
In SQL, the numeric comparison operators (=, <, >, <=, >=, <>) don’t need to be separated by a space, instead, all the other operators (AND/OR/LIKE/IN/...) need to be separated by a space or a carriage return. To make more readable an SQL expression, you can add as many spaces and carriage returns as you want. The following two SQL assertions produce the same result.
@best_back_price>10 AND @yob>=2018
@best_back_price > 10
AND
@yob >= 2018
There is a group of variables that are especially important in Ipposnif: the index variables.
Every time Ipposnif checks the result of an SQL expression or SQL assertion, it provides the IDs (unique identifiers) of the strategy, market, and runner that it is checking at that moment. The image below is a visual explanation of this concept.
@id_strategy contains the ID of the strategy checked at that moment.
@id_race contains the ID of the market checked at that moment.
@id_horse contains the ID of the selection (runner/team/player/etc..) checked at that moment.
Using these variables as parameters passed to SQL functions, we can access any information we need in our strategies: a horse/race/strategy property, the size of the next bet in a betting progression, how much liquidity there is in a market, and much more.
So, in addition to the SQL variable, we have another important SQL entity: the SQL function.
An SQL function is a piece of SQL code that accepts one or more values (or variables) as input, processes these values, and returns the calculated value as output.
For example, if we want to know the official rating of the runner checked at that moment we can use the function dbo.fn_HorseProperty. This function expects two parameters in input: the ID of the runner and the name of the property we want to retrieve.
The ID of the runner is returned by @id_horse (one of the index variables explained above), and the name of the property is a specific string value, in this case, it is 'official_rating'.
dbo.fn_HorseProperty(@id_horse, 'official_rating')
The result of this function can be used in an SQL assertion. In the example below, we want to bet only horses that have an official rating greater than 100.
dbo.fn_HorseProperty(@id_horse, 'official_rating') > 100
The result of a function can be used also in the SQL expressions which define the size or the price of a bet. In the following example, if the runner official rating is lower than 100 we want to bet 2€, if it is greater we want to bet 5€.
CASE WHEN dbo.fn_HorseProperty(@id_horse, 'official_rating') < 100 THEN 2 ELSE 5 END
CASE is a powerful SQL command that allows us to add intelligence to our SQL expressions. It evaluates a condition and returns one of the multiple possible result expressions.
The syntax is:
CASE WHEN {condition to evaluate} THEN {value returned when condition is true} ELSE {value returned when condition is false} END
The condition to evaluate and the two possible results can be any SQL expression, and they can contain other nested CASE expressions.
From Ipposnif you can access Expression Watcher, a useful tool that allows you to watch in real-time the value of a variable or a function, and to debug your strategies.
From the Ipposnif strategies, you can use all the available Microsoft SQL Server functions, plus all the Ipposnif variables and functions dedicated to the exchange betting.
If a variable or a function you need is missing, you can read from here how to add your own.