HUSNG min stack for reports

[Wisher]

Hello,

I am trying to build a stat for reporting purposes that has the tourney effective stack in BB.
I know there is already one available, but I don't like the fact that when you get a walk from the BB your effective stack is 0. I want to be able to analyze how much my opponents are folding from SB with certain stack sizes which the current effective stack column does not give me as an opportunity.

I was thinking I can get this information by getting the min stack at the table and dividing by the BB size. However, amt_before seems to always return the stack size of the active player.
Is there a way I can get the stack size for both players (I am playing ONLY HUSNGs) and get a minimum of that? I guess this can happen in a variable?
I can see there is already a stat and a column for Live Min Stack, but this concerns HUDs, not reports.
Is there any way I can transfer this Live stat for a report?

tl;dr: Looking for a way to determine the min stack for a hand on a tourney table for a report.

Thanks,
Wisher

[kraada]

When your opponent open folds from the SB, he still has a nonzero effective stack. The only player with the effective stack of 0 is the BB.

So if you created a stat that was open fold % for a certain stack size, it would display properly for the SB using the existing effective stack values.

[Wisher]

Thanks for the reply.
However, I am trying to create a report from BB's perspective. And since HUSNGs are all about effective stacks I don't really care about the stack size itself.
I figured that a standard SQL which goes like below will return the stack sizes of both opponents. And I do a min() on those results, I will get the minimum stack which I can then divide by tourney_blinds.amt_bb (if I recall the table name correctly) and get the effective stack size for the hand, not for a particular player.

Code: Select all

SELECT amt_before FROM tourney_hand_player_statistics WHERE id_hand = THE_ID_OF_THE_HAND_I_AM_TRYING_TO_CALCULATE_EFF_STACKS_FOR

Do you have any suggestions as to how I can express THE_ID_OF_THE_HAND_I_AM_TRYING_TO_CALCULATE_EFF_STACKS_FOR in the PT4 statistics creation tool?

[kraada]

From BB's perspective absolutely nothing happens in hands where he gets a walk. Is there a reason you don't want to see this from SB's perspective since he's the one actually making the decision here (to limp or fold)?

I think your method might work though if you used an alias for tourney_hand_player_statistics and an outer reference for id_hand - tourney_hand_player_statistics.id_hand is already the hand in question when you're at the report running stage.

[pt4pt4pt4]

I think I understand what he is looking for, kinda like the inverse of Fold equity.

Some HU players start folding from SB at certain stack sizes (some early stages regardless of stack sizes).

Detecting the trigger point can help you adjust your play to exploit it.


This may be related, but is it possible to get the list of all players stack sizes in a hand to identify your present position at the SNG table in chips?

[kraada]

In that case you'd still want the stat on the SB who is doing the folding - you care about how often he folds at different stack sizes. If you want to do a custom report to look at general populations you can still look at it from the SB perspective - just use an all players report and add a filter for Not(Player is Hero).

You can't get the stacks from everyone in a single hand.

[pt4pt4pt4]

In that case you'd still want the stat on the SB who is doing the folding - you care about how often he folds at different stack sizes. If you want to do a custom report to look at general populations you can still look at it from the SB perspective - just use an all players report and add a filter for Not(Player is Hero).

You can't get the stacks from everyone in a single hand.

I just ran a report on the SB folding at all the different stacks sizes HU but there was no option to include effective stack size.

Is it possible to display the effective preflop stack size of the SB when he folds?

[kraada]

Sure, effective stack size is in the filters in the Hand Details -> Pot Size & Stack Depth area.

If you wanted it broken down by effective stack size like the Preflop Stack Size stat, create a custom version of the val_p_stack_size column replacing amt_before with amt_p_effective_stack and make your copy of the stat point to the new column. If you don't change anything else but that part of the column expression and duplicate the stat and point it at the new column you won't have to do anything fancy.

[BillGatesIII]

This may be related, but is it possible to get the list of all players stack sizes in a hand to identify your present position at the SNG table in chips?

I thought this is a cool idea so I created a computed column to get it done.

First, create a function in pgAdmin.

Code: Select all

CREATE OR REPLACE FUNCTION current_position(tourney_hand_player_statistics)
  RETURNS bigint AS
$BODY$
select all_players.cur_pos
from (select thps.id_player, row_number() over (order by thps.amt_before - thps.amt_blind - thps.amt_ante + thps.amt_won desc) as cur_pos
      from tourney_hand_player_statistics thps
      where thps.id_hand = (select max(ths.id_hand)
                            from tourney_hand_summary ths
                            where ths.id_tourney = $1.id_tourney)) as all_players
where all_players.id_player = $1.id_player;
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION current_position(tourney_hand_player_statistics)
  OWNER TO postgres;


Then create a column with this expression.

Code: Select all

min(tourney_hand_player_statistics.current_position)

Put the column in a stat and you're ready to go

If two or more players have the same amount of chips, they will randomly get assigned a position (like number four and five in the picture). If you want to give them the same number, replace ROW_NUMBER() with RANK() in the function.

[pt4pt4pt4]

When i tried to create a new function in PGadminIII I get this error:

An error has occurred: ERROR: Syntax error at or near "CREATE" LINE 2: $BODY$CREATE OR REPLACE FUNCTION

Code: Select all

CREATE OR REPLACE FUNCTION current_position(tourney_hand_player_statistics)
  RETURNS bigint AS
$BODY$
select all_players.cur_pos
from (select thps.id_player, row_number() over (order by thps.amt_before -

thps.amt_blind - thps.amt_ante + thps.amt_won desc) as cur_pos
      from tourney_hand_player_statistics thps
      where thps.id_hand = (select max(ths.id_hand)
                            from tourney_hand_summary ths
                            where ths.id_tourney = $1.id_tourney)) as all_players
where all_players.id_player = $1.id_player;
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION current_position(tourney_hand_player_statistics)
  OWNER TO postgres;


One thread, somewhat related, mentioned a missing language pack?

I am running version 9 postgres.

Any ideas?