def getCompareRTOTDataForTestIds(self, test_id_1, test_id_2):
ta1 = select([
tests_overall_data.c.timestamp,
func.row_number().over(
order_by=tests_overall_data.c.timestamp).label('rown'),
tests_overall_data.c.avg,
tests_overall_data.c.median,
]).where(tests_overall_data.c.test_id == test_id_1)
ta2 = select([
tests_overall_data.c.timestamp,
func.row_number().over(
order_by=tests_overall_data.c.timestamp).label('rown'),
tests_overall_data.c.avg,
tests_overall_data.c.median,
]).where(tests_overall_data.c.test_id == test_id_2)
t1 = ta1.alias('t1')
t2 = ta2.alias('t2')
statement = select([
t1.c.timestamp.label('timestamp'),
t1.c.avg.label('avg_test_id_' + str(test_id_1)),
t1.c.median.label('med_test_id_' + str(test_id_1)),
t2.c.avg.label('avg_test_id_' + str(test_id_2)),
t2.c.median.label('med_test_id_' + str(test_id_2))
]).where(t1.c.rown == t2.c.rown)
return self.execute_statement(statement, True)
def latest_version_ind_expression(self) -> ColumnElement:
return case({1: 1},
value=func.row_number().over(
partition_by=self.columns_in_table(
self.table, self.parent.key_columns),
order_by=self.table.c.sat_load_dt.desc()),
else_=0)
def get_project_history(self, project_name):
'''retrieve all project data (Response times, CPU load'''
stmt = select([
tests.c.start_time,
func.row_number().over(order_by=tests.c.start_time).label('rown'),
tests.c.display_name,
func.avg(aggregate.c.average).label('Average'),
func.avg(aggregate.c.median).label('Median')
]).where(tests.c.project == project_name) \
.where(aggregate.c.test_id == tests.c.id) \
.group_by(tests.c.display_name) \
.group_by(tests.c.start_time) \
.order_by(asc(tests.c.start_time))
s = stmt.alias('s')
statement = select([ #s.c.start_time,
#func.concat(s.c.rown, '. ', s.c.display_name).label('Release'),
s.c.display_name.label('Test name'),
s.c.Average,
s.c.Median
])
#elif data == 'agg_cpu_load':
# stmt = select([
# tests.c.start_time,
# tests_monitoring_data.c.server_name,
# func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD')
# ]).where(tests.c.project == project_name) \
# .where(tests_monitoring_data.c.test_id == tests.c.id) \
# .group_by(tests.c.display_name) \
# .group_by(tests_monitoring_data.c.server_name) \
# .group_by(tests.c.start_time) \
# .order_by(asc(tests.c.start_time))
# s = stmt.alias('s')
return self.execute_statement(statement, True)
def get_compare_tests_avg(self, test_id, num_of_prev_tests):
num_of_tests = int(num_of_prev_tests)
project_stmt = select([tests.c.project]).where(tests.c.id == test_id)
ps = project_stmt.alias('ps')
stmt = select([
func.row_number().over(order_by=desc(tests.c.start_time)).label('rown'),
tests.c.start_time,
tests.c.display_name,
func.avg(aggregate.c.average).label('average'),
func.avg(aggregate.c.median).label('median')
]).where(tests.c.project == ps.c.project) \
.where(tests.c.id <= test_id) \
.where(tests.c.id == aggregate.c.test_id) \
.group_by(tests.c.display_name) \
.group_by(tests.c.start_time) \
.order_by(desc(tests.c.start_time))
s = stmt.alias('s')
statement = select([ #s.c.start_time,
#func.concat(s.c.rown, '. ', s.c.display_name).label('Release'),
s.c.display_name,
s.c.average,
s.c.median
]).limit(num_of_tests + 1)
return self.execute_statement(statement, True)
def user(error_msg=""):
cur_usr_transactions = db.session.query(
Transaction,
func.row_number().over(order_by=Transaction.date).label("row_num"),
Transaction.date,
Transaction.delta).filter(Transaction.card_id == current_user.id)
# cur_usr_transactions = Transaction.query.order_by(Transaction.date).filter(Transaction.card_id == current_user.id)
currency = Currency.query.all()
return render_template('user.html',
cur_usr_transactions=cur_usr_transactions,
currency=currency,
error_msg=error_msg)
def active_passes(cls):
last_visits = db.session.query(
Visit,
func.row_number().over(
partition_by=Visit.passport_id,
order_by=desc(Visit.timestamp)).label('row_number')) \
.subquery('lv')
open_visits = db.session.query(Visit) \
.select_entity_from(last_visits) \
.filter(last_visits.c.row_number == 1) \
.filter(last_visits.c.check_out == None) \
.subquery()
open_passports = db.session.query(Passport).join(open_visits)
return open_passports
def get_prev_test_id(self, test_id, offset):
project_stmt = select([tests.c.project]). \
where(tests.c.id == test_id)
ps = project_stmt.alias('ps')
stmt = select([
func.row_number().
over(order_by=desc(tests.c.start_time)).
label('rown'),
tests.c.id
]).where(tests.c.project == ps.c.project) \
.where(tests.c.id <= test_id)\
.order_by(desc(tests.c.start_time))
s = stmt.alias('s')
statement = select([s.c.id]).where(s.c.rown == offset)
return self.execute_statement(statement, True)
def __init__(self,
session,
widget_query,
num_splits=10,
table_name="tmp_widget_random"):
from sqlalchemy import Column
from sqlalchemy.sql.expression import func, select
t_w = schema.widget
class tmp_widget_random(schema.TableBase):
idfold = Column(t_w.idwidget.type,
nullable=False,
primary_key=True)
idwidget = Column(t_w.idwidget.type,
nullable=False,
primary_key=True)
__table_args__ = ({
'prefixes': ["TEMPORARY"],
'keep_existing': True
}, )
__tablename__ = table_name
self.num_splits = int(num_splits)
self.session = session
self.tmp_wr = tmp_widget_random
self.tmp_wr.__table__.create(bind=self.session.bind)
if self.session.bind.dialect.name.lower() == "mysql":
randfunc = func.rand()
else:
randfunc = func.random()
num_rows = widget_query.count()
row_number_column = func.row_number().over(
order_by=randfunc).label('row_number')
self.session.execute(
self.tmp_wr.__table__.insert().from_select(
[self.tmp_wr.idfold, self.tmp_wr.idwidget],
select([((row_number_column-1) * int(self.num_splits)) / int(num_rows), t_w.idwidget]) \
.where(t_w.idwidget.in_(widget_query)) \
))
def get_bounded_overall_compare_data_for_project_name(
self, project_name, data, test_id_min, test_id_max):
log.debug("Get bounded overall data for project_name: " +
project_name + ", test_id_min: " + str(test_id_min) +
"; test_id_max: " + str(test_id_max) + ";")
if data == 'agg_response_times':
stmt = select([
tests.c.start_time,
func.row_number().over(order_by=tests.c.start_time).label('rown'),
tests.c.display_name,
func.avg(aggregate.c.average).label('Average'),
func.avg(aggregate.c.median).label('Median')
]).where(tests.c.project == project_name) \
.where(aggregate.c.test_id == tests.c.id) \
.where(between(tests.c.id, int(test_id_min), int(test_id_max))) \
.group_by(tests.c.display_name) \
.group_by(tests.c.start_time) \
.order_by(asc(tests.c.start_time))
s = stmt.alias('s')
statement = select([
s.c.start_time,
func.concat(s.c.rown, '. ', s.c.display_name).label('Release'),
s.c.Average, s.c.Median
])
elif data == 'agg_cpu_load':
stmt = select([
tests.c.start_time,
tests.c.display_name.label('Release'),
tests_monitoring_data.c.server_name,
func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD')
]).where(tests.c.project == project_name) \
.where(tests_monitoring_data.c.test_id == tests.c.id) \
.where(between(tests.c.id, int(test_id_min), int(test_id_max))) \
.group_by(tests.c.display_name) \
.group_by(tests_monitoring_data.c.server_name) \
.group_by(tests.c.start_time) \
.order_by(asc(tests.c.start_time))
s = stmt.alias('s')
statement = select([s.c.start_time, s.c.server_name, s.c.CPU_LOAD])
return self.execute_statement(statement, True)
from sqlalchemy.sql.expression import and_, func, literal
from pygotham_2019 import meta, model, sales
from pygotham_2019.utils import print_table
if __name__ == "__main__":
sales.setup()
latest_sales_sq = (meta.session.query(
model.Sale.id,
func.row_number().over(
partition_by=model.Sale.store_id,
order_by=model.Sale.date.desc(),
).label("row_number"),
)).subquery("latest_sales")
latest_sales = (meta.session.query(model.Sale).join(
latest_sales_sq,
and_(
model.Sale.id == latest_sales_sq.c.id,
latest_sales_sq.c.row_number == 1,
)).order_by(model.Sale.id))
print("window functions to find the latest sale at each store")
print_table(latest_sales)
print("")
sales_by_store = (meta.session.query(
model.Sale.store_id.label("store_id"),
func.sum(model.Sale.amount).label("amount"),
