async def get_units_trained(keys, context):
"""Get counts of units trained bucketed by interval."""
where, values = compound_where(keys, ('match_id', 'player_number'))
query = """
select player_number, x.match_id, objects.id as object_id, name, count, inter as timestamp, extract(epoch from inter)::integer as timestamp_secs
from
(
select oi.match_id, player_number, ois.dataset_id,
case
when ois.object_id=any(:villager_ids) then :normalized_villager_id
when ois.object_id=any(:monk_ids) then :normalized_monk_id
else ois.object_id
end as obj_id,
to_timestamp(floor((extract('epoch' from created) / :interval )) * :interval) as inter,
count(ois.instance_id) as count
from object_instance_states as ois join
(
select min(id) as id,min(timestamp) from object_instance_states
where ({}) and player_number > 0 and class_id=70 and not (object_id=any(:herdable_ids))
group by instance_id
) as s on ois.id=s.id
join object_instances as oi on ois.instance_id=oi.instance_id and oi.match_id=ois.match_id
where oi.created > '00:00:10'
group by player_number, obj_id, ois.dataset_id, oi.match_id, inter
order by count desc
) as x join objects on x.obj_id=objects.id and x.dataset_id=objects.dataset_id
order by timestamp, name
""".format(where)
results = await context.database.fetch_all(query, values=dict(values, interval=300, **NORMALIZE_VALUES))
return by_key(results, ('match_id', 'player_number'))
async def odds_query(database,
teams,
type_id,
match_filters=None,
civ_filter=False,
user_filter=False): # pylint: disable=too-many-arguments, too-many-locals
"""Run a query with odds constraints."""
start_time = time.time()
team_size = 'v'.join(str(len(t)) for t in teams.values())
values = {'team_size': team_size, 'type_id': type_id}
match_query = """
select matches.id, players.winner, players.{}
from matches join players on matches.id=players.match_id
"""
for i, team in teams.items():
if user_filter and civ_filter:
key = 'civilization_id'
keys = [(p['user_id'], p['civilization_id']) for p in team]
player_filters, player_values = compound_where(
keys, ('players.user_id', 'players.civilization_id'))
values.update(player_values)
elif civ_filter:
key = 'civilization_id'
player_filters = " players.civilization_id=any(:civilization_ids_{})".format(
i)
values.update({
'civilization_ids_{}'.format(i):
[p['civilization_id'] for p in team]
})
elif user_filter:
key = 'user_id'
player_filters = " players.user_id=any(:user_ids_{})".format(i)
values.update(
{'user_ids_{}'.format(i): [p['user_id'] for p in team]})
team_query = """
select match_id from players
where {}
group by match_id, team_id
having count(distinct players.{}) = {}
""".format(player_filters, key, len({p[key]
for p in team}))
match_query += " join ({0}) as t{1} on matches.id=t{1}.match_id".format(
team_query, i)
match_query += " where matches.team_size=:team_size and matches.type_id=:type_id"
match_query = match_query.format(key)
if match_filters is not None:
match_query += ' and ' + match_filters[0]
values.update(match_filters[1])
result = await database.fetch_all(match_query, values=values)
result = compute_odds(by_key(result, 'id').values(), key, teams)
LOGGER.debug("computed odds in %f", time.time() - start_time)
return result
async def get_research_by_player(keys, context):
"""Get researches."""
where, values = compound_where(keys, ('match_id', 'player_number'))
query = """
select name, started::interval(0), finished::interval(0), player_number, match_id,
extract(epoch from started)::integer as started_secs, extract(epoch from finished)::integer as finished_secs
from research join technologies on research.technology_id=technologies.id and research.dataset_id=technologies.dataset_id
where {}
order by started
""".format(where)
results = await context.database.fetch_all(query, values=values)
return by_key(results, ('match_id', 'player_number'))
async def get_villager_allocation(keys, context):
"""Get villager allocation per player bucketed by interval."""
where, values = compound_where(keys, ('match_id', 'player_number'))
query = """
select
vils.match_id, vils.player_number, vils.res as name, buckets.inter as timestamp,
extract(epoch from buckets.inter)::integer as timestamp_secs, count(distinct vils.instance_id)
from (
with subquery as (
select row_number() over (order by x.instance_id, inter), x.match_id, ois.player_number, x.instance_id, x.inter, objects.name, oi.created, oi.destroyed,
case
when object_id = any(:food_vils) then 'Food'
when object_id = any(:wood_vils) then 'Wood'
when object_id = any(:gold_vils) then 'Gold'
when object_id = any(:stone_vils) then 'Stone'
else 'idle'
end as res
from (
select max(id) as id, match_id, instance_id, make_interval(secs => floor((extract('epoch' from timestamp) / :interval )) * :interval) as inter
from object_instance_states
where object_id = any(:resource_vils)
and ({where})
group by instance_id, inter, match_id
) as x join object_instance_states as ois on x.match_id=ois.match_id and x.id=ois.id
join object_instances oi on oi.match_id=ois.match_id and oi.instance_id=ois.instance_id
join objects on objects.id=ois.object_id and objects.dataset_id=ois.dataset_id
)
select f.*, l.inter as next from
subquery as f left join subquery as l on f.row_number=l.row_number-1 and f.instance_id=l.instance_id
) as vils join (
select make_interval(secs => floor((extract('epoch' from timestamp) / :interval )) * :interval) as inter
from object_instance_states as ois
where ({where})
group by inter
order by inter
) as buckets on
vils.inter <= buckets.inter and (vils.next is null or vils.next > buckets.inter) and
vils.created <= (buckets.inter + (:interval * interval '1 second')) and
(vils.destroyed is null or vils.destroyed >= buckets.inter)
group by vils.match_id, vils.player_number, vils.res, buckets.inter
order by buckets.inter, vils.player_number, vils.res
""".format(where=where)
results = await context.database.fetch_all(query, values=dict(
values,
resource_vils=RESOURCE_VILLAGER_IDS,
food_vils=FOOD_VILLAGER_IDS,
wood_vils=WOOD_VILLAGER_IDS,
gold_vils=GOLD_VILLAGER_IDS,
stone_vils=STONE_VILLAGER_IDS,
interval=300
))
return by_key(results, ('match_id', 'player_number'))
async def get_transactions(keys, context):
"""Get transactions."""
where, values = compound_where(keys, ('players.match_id', 'player_number'))
query = """
select
players.match_id, timestamp::interval(0), extract(epoch from timestamp)::integer as timestamp_secs,
player_number,
case when action_id=123 then 'Gold' else resources.name end as sold_resource,
(amount * 100) as sold_amount,
case when action_id=123 then resources.name else 'Gold' end as bought_resource
from players left join transactions on transactions.match_id=players.match_id and transactions.player_number = players.number
join resources on transactions.resource_id=resources.id
where {}
order by timestamp
""".format(where)
results = await context.database.fetch_all(query, values=values)
return by_key(results, ('match_id', 'player_number'), defaults=keys)
async def get_timeseries(keys, context):
"""Get timeseries data."""
where, values = compound_where(keys, ('match_id', 'player_number'))
query = """
select
player_number, match_id, timestamp, population, military,
percent_explored, relic_gold, total_food, total_gold, total_stone,
total_wood, trade_profit, value_current_units, value_lost_buildings,
value_lost_units, value_objects_destroyed, value_spent_objects,
value_spent_research, extract(epoch from timestamp)::integer as timestamp_secs,
tribute_sent, tribute_received, kills, deaths, razes,
kills - deaths as kd_delta,
case when value_lost_units+value_lost_buildings > 0 then value_objects_destroyed/(value_lost_units+value_lost_buildings)::float else 0.0 end as damage,
case when value_spent_research > 100 then value_objects_destroyed/(value_spent_research)::float * 100 else 0.0 end as roi
from timeseries
where {}
order by timestamp
""".format(where)
results = await context.database.fetch_all(query, values=values)
return by_key(results, ('match_id', 'player_number'))
async def get_player(keys, context):
"""Get basic player data."""
where, values = compound_where(keys, ('match_id', 'number'))
query = """
select
players.match_id, players.number, players.name, players.winner, players.color_id,
players.user_id, players.platform_id, players.user_name
from players
where {}
""".format(where)
results = await context.database.fetch_all(query, values=values)
return {(player['match_id'], player['number']):
dict(match_id=player['match_id'],
number=player['number'],
name=player['name'],
color_id=player['color_id'],
winner=player['winner'],
user=dict(id=player['user_id'],
name=player['user_name'],
platform_id=player['platform_id']))
for player in results}