This section will describe some of the common elements that make up an expression in a Column.
All of the elements described here can be extended and combined to give more complex expressions, should the Statistic that you are building need that.
Expressions can consist of database fields, functions and operators, including some PostgreSQL functions.
The best ways to explore the options are to examine existing columns' Expressions and to use the blue insert link above and to the right of the Expression field.
An Expression can be as simple as retrieving a single value from a field in the database.
For instance, in the Holdem Cash Hand section you might want a stat to display a checkbox to indicate whether or not the player limped preflop in a hand.
The expression for this would just be:
Similarly, you might want a stat to display the total amount the player bet in a hand.
The expression for this would be:
Use of 'IF'
The IF function is used to determine whether a condition is true or false, and return a different value based on the outcome.
The structure of the IF function is described above, but it is basically:
- IF[ expression, then, else ]
For instance, if you want a stat in the Holdem Cash Hand section to show the size of a continuation bet on the flop but to show zero for non-continuation bets you could use an expression like this:
- if[ holdem_hand_player_statistics.flg_f_cbet, holdem_hand_player_detail.amt_f_bet_made, 0 ]
The IF function is also commonly used in combination with SUM to generate a count of how many times a certain situation occurs across all hands - for instance in the Holdem Cash Player Statistics section.
I will describe the use of SUM next, but for now let's see how IF can be used to turn a boolean value into a 1 (true) or 0 (false).
Using the preflop limp example from above:
- if[ holdem_hand_player_statistics.flg_p_limp, 1, 0 ]
So now let's move on to the SUM function to see how we can count the number of hands where the player limped preflop.
Use of 'SUM'
The SUM function is used to add up a set of values.
It is therefore only useful is sections where we are dealing with more than one item, such as Holdem Cash Player Statistics which generates statistics based on all of a player's hands.
Before we get back to counting the number of hands where a player limped preflop, here is a simple expression of the use of SUM.
To calculate the total amount of money a player bet across all hands, we could use an expression in the Holdem Cash Player Statistics section:
- sum( holdem_hand_player_detail.amt_bet_ttl )
Compare that with the expression we saw in the Simple Expressions section above which just retrieved the amount bet in a single hand, and which would be used in the Holdem Cash Hand section.
Combining SUM and IF
So now we know how to use SUM and IF we can combine them to write a column expression to count the number of times a player limped preflop.
Again, this would be in the Holdem Cash Player Statistics section:
- sum( if[holdem_hand_player_statistics.flg_p_limp, 1, 0] )
The SUM statement then adds up these 1s and 0s to give us a count.
You can combine IF statements if you have more than 2 outcomes to generate.
As a simple example let's say we want a column to determine whether the player bet more or less than the pot size on the flop or didn't bet at all, which gives us 3 outcomes. You would normally do this sort of thing in the Format Expression of the Statistic, rather than in the Column itself, but it should illustrate the point..
- if[ holdem_hand_player_detail.val_f_bet_made_pct = 0, 'Did not bet', if[ holdem_hand_player_detail.val_f_bet_made_pct > 100, 'Bet more than the pot', 'Bet less than the pot' ] ]
You can use the normal mathematical operators in expressions:
- + - / * = < > >= <=
!= (not equal: <> also works)
- holdem_hand_player_detail.val_f_bet_made_pct > 100
- % - modulo (remainder) 5 % 4 = 1
^ - exponentiation (power) 2 ^ 3 = 8
abs - absolute value. abs(-5) = 5
round - round to the nearest integer (whole number) round(42.7) = 43; or you can specify the number of decimal places: round( 66.66666, 2 ) = 66.67
trunc - truncate towards zero. trunc(42.7) = 42; or you can specify the number of decimal places: trunc( 66.666666, 1 ) = 66.6
You can use the normal boolean operators in expressions:
- AND OR NOT
- holdem_hand_player_statistics.flg_f_cbet AND holdem_hand_player_statistics.flg_t_cbet
- holdem_hand_player_statistics.flg_f_cbet AND NOT holdem_hand_player_statistics.flg_t_cbet
- holdem_hand_player_statistics.flg_f_bet OR holdem_hand_player_statistics.flg_t_bet
Normal operator precedence applies in expressions in PT3.
Some operators have higher precedence than others so will always be calculated first, regardless of the order they are written in.
- 2 + 3 * 4 = 14
Brackets over-ride precendence, and you should use them to clarify exactly what you are trying to do.
- 2 + 3 * 4 = 14
(2 + 3) * 4 = 20
- 5 + 4 / 2 + 1 = 5 + (4 / 2) + 1 = 8
(5 + 4) / (2 + 1) = 3
For example, if you want to see if a player bet the flop and then bet the turn or river, you could use:
- holdem_hand_player_statistics.flg_f_bet AND (holdem_hand_player_statistics.flg_t_bet OR holdem_hand_player_statistics.flg_r_bet)
If you want to see whether a player bet and then folded on the flop, or bet and then folded on the turn, you could use:
- (holdem_hand_player_statistics.flg_f_bet AND holdem_hand_player_statistics.flg_f_fold) OR (holdem_hand_player_statistics.flg_t_bet AND holdem_hand_player_statistics.flg_t_fold)
String Expressions and Functions
The PT3 database contains some string (text) fields, and these can be read and analysed in Column Expressions.
Some typical string fields are:
- Player names - player.player_name
- Limit names - holdem_limit.limit_name
- Player actions by street - lookup_actions_p.action (see below)
- Player action in specific situations - holdem_hand_player_statistics.enum_f_cbet_action (the action a player took when facing a flop continuation bet)
- Street on which something occurred - holdem_hand_player_statistics.enum_allin (the street on which a player went all-in)
Player actions are the most commonly used of these, so I'll describe them in more detail, and use them for the examples here.
Every action is represented by a single uppercase letter.
X = Check, F = Fold, C = Call, B = Bet, R = Raise
The enum_ fields consist of a single character; either one of the above action characters or a character to represent a street, depending on the type of field.
P = Preflop, F = Flop, T = Turn, R = River, N = None (e.g. if the player did not go all in then holdem_hand_player_statistics.enum_allin = 'N')
The player actions by street strings (like lookup_actions_p.action) consist of a series of characters to represent all of a player's actions on a single street.
For example, if a player limps then folds to a raise preflop the action string would be 'CF'.
If they check-raised the flop then called a 3-bet their action string would be 'XRC'.
Exact string comparisons
We can compare whole strings using the normal = operator.
For example, if we want to see if a player's only action preflop was to call we could use:
- lookup_actions_p.action = 'C'
- Tip! Strings are always written inside single quote marks like this.
To check just certain parts of a string we need to use Pattern Matching functions.
Pattern Matching string comparisons - LIKE
To compare just part of a string the normal = operator does not work, as mentioned above.
If we want to check whether the player's first action preflop was to call, but we don't care what they did after that, then we could use the LIKE function:
- lookup_actions_p.action LIKE 'C%'
The % is a token which makes up part of a pattern. % specifically represents any (zero or more) characters, which can be anything.
If we want to check for the player calling and then making exactly one more action (but we don't care what that second action is) then we would use the _ (underscore) token which represents exactly one character, which can be anything.
- lookup_actions_p.action LIKE 'C_'
- lookup_actions_p.action LIKE 'C_%'
You can use these tokens at any point in the string, so you could check for a player being the preflop aggressor by checking for their preflop action string ending with a raise:
- lookup_actions_p.action LIKE '%R'
- holdem_limit.limit_name LIKE '%(6 max)'
This gets a bit more complicated now, so if you find LIKE confusing enough, you might want to skip the next section for now..
Pattern Matching string comparisons - SIMILAR TO
If we want to check for a player's action when facing a flop bet being a call, but we don't care whether they checked before that or not (i.e. whether the call is made in or out of position) or what actions, if any, they made after calling we could do this with LIKE, but we would need two checks, like this: lookup_actions_f.action LIKE 'C%' OR lookup_actions_f.action LIKE 'XC%'
Alternatively we could use a single SIMILAR TO expression:
- lookup_actions_f.action SIMILAR TO '(X|XC)%'
Please see the PostgreSQL documentation on Pattern Matching for more information.
Another way of working with strings is the SUBSTRING function.
This allows you to access any part of a string - its format is this:
- SUBSTRING( string FROM x FOR y )
- SUBSTRING( 'abcdefg' from 2 for 3 ) = 'bcd'
- SUBSTRING( lookup_actions_p.action from 1 for 2 ) = 'CC'
In general LIKE is easier to use than SUBSTRING for comparisons, but SUBSTRING can also be used to return part of a string.
For instance, if you want to retrieve the first character of a limit name to display the currency you could use:
- SUBSTRING( holdem_limit.limit_name from 1 for 1 )