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"),