Nth root stat

[gazavat]

I want to create a stat that has in its formula the following:

nth root of X, where X is a known number and N is the number of players left to act behind the Raised first in player.

1 Please tell me what function to use for nth root of a number.

2 Help me create an expression for N

[WhiteRider]

The PostgreSQL maths functions and operators can be found here - I think you should be able to do what you want with "power", although we're a bit short-handed this weekend and I don't have time to experiment with this right now.

If the stat is not from the point of view of the first raiser you can get the position of the first raiser using this expression:
substring(cash_hand_summary.str_aggressors_p from 2 for 1)::int

Assuming both blinds have been posted the number of players who haven't acted yet will be:
2 + substring(cash_hand_summary.str_aggressors_p from 2 for 1)::int

To check that the first raise was an open-raise you can use:

substring(cash_hand_summary.str_actors_p from 1 for 1) = substring(cash_hand_summary.str_aggressors_p from 2 for 1)

[gazavat]

power( cnt_avg_3B_behind, 1/(2 + substring(cash_hand_summary.str_aggressors_p from 2 for 1)::int))

It says invalid SQL

If I replace (2 + substring(cash_hand_summary.str_aggressors_p from 2 for 1)::int) with an integer, it says valid expression, so it does recognize the power function.

[WhiteRider]

Is that in a column or a stat?
You will only be able to use the expression I gave you in a column (because it accesses a database table), but since you're using another column name there it looks like this may be in a stat?
When you're at the stat level there is no concept of "players left to act" because it is looking at results across all hands (the columns access information about each hand and generate a total results for use in the stat).

What exactly are you trying to calculate?

[gazavat]

Is that in a column or a stat?
You will only be able to use the expression I gave you in a column (because it accesses a database table), but since you're using another column name there it looks like this may be in a stat?
When you're at the stat level there is no concept of "players left to act" because it is looking at results across all hands (the columns access information about each hand and generate a total results for use in the stat).

It is a column. I can use another column in a column expression? The SQL problem is with the substring denominator as replacing it with a number validated the expression. You can replace my custom column with any number to test the expression.

Any ideas whats wrong with the SQL?

[gazavat]

I also tried creating a column for 2 + substring(cash_hand_summary.str_aggressors_p from 2 for 1)::int and then using that in the column expression above, but it did not help. Maybe it does not like the output format of the substring?

FWIW, the power function does not seem to be recognized in stat expression, so that is why I am doing all the calculations in a custom column. Not sure why is that.

[WhiteRider]

Is this for cash or tournaments?
If you're playing tournaments you need to replace "cash_" with "tourney_".

Using another column within a column expression like that won't work. You will need to use the expression from the other column directly.

Using "::int" after the substring expression turns the output into an integer, so should make it valid and I can get an expression including "power" with and the expression I gave you.

[gazavat]

Is this for cash or tournaments?
If you're playing tournaments you need to replace "cash_" with "tourney_".

Cash, no issues there.

Using another column within a column expression like that won't work. You will need to use the expression from the other column directly.

1 Could you explain, why I cannot use columns within column expressions?

2 Also, it seems I cannot use Sum(expression)/Sum(expression)? Why is that?

3 Why is the power function not recognized within the statistics expressions?

[kraada]

(1) This is due to the way the SQL is generated using the columns. You cannot reference one column within another; it will not work.

(2) This is due to the way that the columns have to be converted to work as SQL queries. However, if you're going to do that what you want to do is just create your two sum() pieces as different columns, then divide in the Stat's value expression itself (just as most of the built in stats do).

(3) I believe this is due to the comma which is parsed as being a part of the if[] statement.

[gazavat]

(2) This is due to the way that the columns have to be converted to work as SQL queries. However, if you're going to do that what you want to do is just create your two sum() pieces as different columns, then divide in the Stat's value expression itself (just as most of the built in stats do).

(3) I believe this is due to the comma which is parsed as being a part of the if[] statement.

3) I do not see any if statement?

Here is the issue:

I cannot validate any use of the POWER function within the statistics expression. For some reason some versions validate within the Column expressions. I have no explanation for that and have no idea if it makes sense. However, it prevents me from using the column and stat expressions as intended. That is the only reason I try using the Power function within a column expression. I would much rather create a column for each part of the equation and just do the power expression as a stat using column, but I cannot validate it. Help is highly appreciated.