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 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 St_End(start, end):
start_end_date = session.query(
func.max(Measurement.tobs), func.min(Measurement.tobs),
func.avg(Measurement.tobs)).filter(Measurement.date >= start).filter(
Measurement.date <= end).all()
Start_End = list(np.ravel(start_end_date))
return jsonify(Start_End)
def _top_rated(session, limit, offset=9.90, appendto=tuple()):
# select movie_id, avg(rating) from rating group by movie_id
# having (avg(rating) > 9.90 and avg(rating) <=10.0);
top_offset = min([offset + .1, 10.0])
avg_ = func.avg(Rating.rating)
cnt_ = func.count(Rating.user_id)
res = session.query(Rating.movie_id, avg_)\
.group_by(Rating.movie_id)\
.having(and_(cnt_ > 1, avg_ > offset, avg_ <= top_offset))\
.all()
if len(res) > 0 or offset < 1.0:
res = tuple(res)
appendto = appendto + res
if len(appendto) >= limit or offset < 1.0:
appendto = list(appendto)
shuffle(appendto)
if len(appendto) > limit:
appendto[limit:] = []
return appendto
return Rating._top_rated(session,
limit=limit,
offset=offset - 0.1,
appendto=appendto)
def sort(self, trans, query, ascending, column_name=None):
# Get the columns that connect item's table and item's rating association table.
item_rating_assoc_class = getattr(
trans.model, f'{self.model_class.__name__}RatingAssociation')
foreign_key = get_foreign_key(item_rating_assoc_class,
self.model_class)
fk_col = foreign_key.parent
referent_col = foreign_key.get_referent(self.model_class.table)
# Do sorting using a subquery.
# Subquery to get average rating for each item.
ave_rating_subquery = trans.sa_session.query(fk_col,
func.avg(item_rating_assoc_class.table.c.rating).label('avg_rating')) \
.group_by(fk_col).subquery()
# Integrate subquery into main query.
query = query.outerjoin(
(ave_rating_subquery,
referent_col == ave_rating_subquery.columns[fk_col.name]))
# Sort using subquery results; use coalesce to avoid null values.
if not ascending: # TODO: for now, reverse sorting b/c first sort is ascending, and that should be the natural sort.
query = query.order_by(
func.coalesce(ave_rating_subquery.c.avg_rating, 0).asc())
else:
query = query.order_by(
func.coalesce(ave_rating_subquery.c.avg_rating, 0).desc())
return query
def getReplyStats(username, start_time_t):
retval = {}
retval["min_reply_time_sec"] = session.query(
func.min(Tweets.reply_age).label("min")).filter(
Tweets.username == username).filter(
Tweets.time_t >= start_time_t).filter(
Tweets.reply_tweet_id != None).filter(
Tweets.reply_age != 0).first().min
retval["min_reply_time"] = round(retval["min_reply_time_sec"] / 60, 0)
retval["max_reply_time_sec"] = session.query(
func.max(Tweets.reply_age).label("max")).filter(
Tweets.username == username).filter(
Tweets.time_t >= start_time_t).filter(
Tweets.reply_tweet_id != None).filter(
Tweets.reply_age != 0).first().max
retval["max_reply_time"] = round(retval["max_reply_time_sec"] / 60, 0)
retval["avg_reply_time_sec"] = session.query(
func.avg(Tweets.reply_age).label("avg")).filter(
Tweets.username == username).filter(
Tweets.time_t >= start_time_t).filter(
Tweets.reply_tweet_id != None).filter(
Tweets.reply_age != 0).first().avg
retval["avg_reply_time_sec"] = round(retval["avg_reply_time_sec"], 2)
retval["avg_reply_time"] = round(retval["avg_reply_time_sec"] / 60, 0)
median_stats = getReplyStatsMedian(start_time_t, retval)
retval = {**retval, **median_stats}
return (retval)
def rank_participants():
Application.standardize_scores()
answer_values = Application.question_answer_matrix_subquery()
scores = db.session.query(Application.id, func.sum(answer_values.c.sum_values + Application.standardized_score).label('calculated_score')) \
.join(answer_values, answer_values.c.id == Application.id) \
.group_by(Application.id) \
.subquery()
team_scores = db.session.query(Answer.answer, func.avg(scores.c.calculated_score).label('team_score')) \
.join(scores, scores.c.id == Answer.application_id) \
.join(Question, Question.id == Answer.question_id) \
.filter(Question.question_type == QuestionType.teamEmail) \
.group_by(Answer.answer) \
.subquery()
teamQuestion = aliased(Question)
teamAnswer = aliased(Answer)
results_q = db.session.query(Application.id, Application.date_added, Application.feedback, Application.score, Application.standardized_score, team_scores.c.team_score, func.json_object_agg(Question.question, Answer.answer)) \
.join(Answer) \
.join(Question) \
.join(teamAnswer, teamAnswer.application_id == Application.id) \
.join(teamQuestion, teamQuestion.id == teamAnswer.question_id) \
.join(team_scores, team_scores.c.answer == teamAnswer.answer) \
.filter(teamQuestion.question_type == QuestionType.teamEmail) \
.group_by(Application.id, team_scores.c.team_score) \
.order_by(team_scores.c.team_score.desc().nullslast()) \
results = results_q.all()
return results
def get_mean_stddev_scores_per_user():
"""Returns all scored applications"""
return db.session.query(func.avg(Application.score), func.stddev(Application.score), User.id) \
.filter(Application.score != 0) \
.join(User, (User.id == Application.locked_by) | (User.id == Application.assigned_to)) \
.group_by(User.id) \
.all()
def get_compare_tests_cpu(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.dense_rank().over(
order_by=desc(tests.c.start_time),
).label('rank'),
tests.c.display_name,
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 == ps.c.project) \
.where(tests_monitoring_data.c.test_id <= test_id) \
.where(tests.c.id == tests_monitoring_data.c.test_id) \
.group_by(tests.c.display_name) \
.group_by(tests_monitoring_data.c.server_name) \
.group_by(tests.c.start_time)
s = stmt.alias('s')
statement = select([
s.c.display_name,
sqlalchemy.func.regexp_replace(s.c.server_name, '\\.',
'_').label('server_name'),
s.c.CPU_LOAD
]).where(s.c.rank <= num_of_tests + 1)
return self.execute_statement(statement, True)
def sort( self, trans, query, ascending, column_name=None ):
def get_foreign_key( source_class, target_class ):
""" Returns foreign key in source class that references target class. """
target_fk = None
for fk in source_class.table.foreign_keys:
if fk.references( target_class.table ):
target_fk = fk
break
if not target_fk:
raise RuntimeException( "No foreign key found between objects: %s, %s" % source_class.table, target_class.table )
return target_fk
# Get the columns that connect item's table and item's rating association table.
item_rating_assoc_class = getattr( trans.model, '%sRatingAssociation' % self.model_class.__name__ )
foreign_key = get_foreign_key( item_rating_assoc_class, self.model_class )
fk_col = foreign_key.parent
referent_col = foreign_key.get_referent( self.model_class.table )
# Do sorting using a subquery.
# Subquery to get average rating for each item.
ave_rating_subquery = trans.sa_session.query( fk_col,
func.avg( item_rating_assoc_class.table.c.rating ).label('avg_rating') ) \
.group_by( fk_col ) \
.subquery()
# Integrate subquery into main query.
query = query.outerjoin( (ave_rating_subquery, referent_col == ave_rating_subquery.columns[fk_col.name]) )
# Sort using subquery results; use coalesce to avoid null values.
if not ascending: # TODO: for now, reverse sorting b/c first sort is ascending, and that should be the natural sort.
query = query.order_by( func.coalesce( ave_rating_subquery.c.avg_rating, 0 ).asc() )
else:
query = query.order_by( func.coalesce( ave_rating_subquery.c.avg_rating, 0 ).desc() )
return query
def sort( self, trans, query, ascending, column_name=None ):
def get_foreign_key( source_class, target_class ):
""" Returns foreign key in source class that references target class. """
target_fk = None
for fk in source_class.table.foreign_keys:
if fk.references( target_class.table ):
target_fk = fk
break
if not target_fk:
raise RuntimeException( "No foreign key found between objects: %s, %s" % source_class.table, target_class.table )
return target_fk
# Get the columns that connect item's table and item's rating association table.
item_rating_assoc_class = getattr( trans.model, '%sRatingAssociation' % self.model_class.__name__ )
foreign_key = get_foreign_key( item_rating_assoc_class, self.model_class )
fk_col = foreign_key.parent
referent_col = foreign_key.get_referent( self.model_class.table )
# Do sorting using a subquery.
# Subquery to get average rating for each item.
ave_rating_subquery = trans.sa_session.query( fk_col,
func.avg( item_rating_assoc_class.table.c.rating ).label('avg_rating') ) \
.group_by( fk_col ) \
.subquery()
# Integrate subquery into main query.
query = query.outerjoin( (ave_rating_subquery, referent_col == ave_rating_subquery.columns[fk_col.name]) )
# Sort using subquery results; use coalesce to avoid null values.
if not ascending: # TODO: for now, reverse sorting b/c first sort is ascending, and that should be the natural sort.
query = query.order_by( func.coalesce( ave_rating_subquery.c.avg_rating, 0 ).asc() )
else:
query = query.order_by( func.coalesce( ave_rating_subquery.c.avg_rating, 0 ).desc() )
return query
def calculate_avg_freshness(db):
avg_timestamps = db.query(
func.to_timestamp(
func.avg(func.extract("epoch",
db_models.Url.url_last_visited)))).first()
rv = (avg_timestamps[0].strftime("%Y-%m-%d %H:%M:%S.%f")
if avg_timestamps[0] is not None else "None")
return rv
async def get_avg_volume(cls, currency_id: int, last_days=None):
query = func.avg(cls.volume).select().where(cls.currency_id == currency_id)
if last_days:
last_days_dt = datetime.utcnow().replace(
hour=0, minute=0, second=0, microsecond=0,
) - timedelta(days=last_days - 1)
query = query.where(Rate.date >= last_days_dt)
return await query.gino.scalar()
def watchlist(self):
watchlist_ids = map(getter_id, self.user.watchlist)
avg_rating = self.session.query(Rating.movie_id, func.avg(Rating.rating))\
.filter(Rating.movie_id.in_(watchlist_ids))\
.group_by(Rating.movie_id).all()
avg_rating = dict(avg_rating)
self.view['avg_rating'] = avg_rating
self.template = 'my/watchlist.phtml'
def watchlist(self):
watchlist_ids = map(getter_id, self.user.watchlist)
avg_rating = self.session.query(Rating.movie_id, func.avg(Rating.rating))\
.filter(Rating.movie_id.in_(watchlist_ids))\
.group_by(Rating.movie_id).all()
avg_rating = dict(avg_rating)
self.view['avg_rating'] = avg_rating
self.template = 'my/watchlist.phtml'
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)
def build_leaderboard(self, category_name):
session = self.Session()
# Prepare category_id
category_id = self.get_category_id(category_name)
# Join Group and Score tables using group_id, filter based on category_id, then select these 7 columns (as subquery)
score_list = (session.query(
self.Group.group_id,
self.Group.name,
self.Score.category_id,
self.Score.criteria_1_score,
self.Score.criteria_2_score,
self.Score.criteria_3_score,
self.Score.criteria_4_score,
).join(self.Score, self.Group.group_id == self.Score.group_id).filter(
self.Score.category_id == category_id).subquery())
# Select group names and average scores as namedtuples for all criterias
result = (session.query(
score_list.c.name,
func.avg(score_list.c.criteria_1_score).label("criteria_1"),
func.avg(score_list.c.criteria_2_score).label("criteria_2"),
func.avg(score_list.c.criteria_3_score).label("criteria_3"),
func.avg(score_list.c.criteria_4_score).label("criteria_4"),
).group_by(score_list.c.name).all())
# Insert percentage of each criteria here
actual_result = ((
group_name,
(decimal.Decimal(CRIT_1) * first) +
(decimal.Decimal(CRIT_2) * second) +
(decimal.Decimal(CRIT_3) * third) +
(decimal.Decimal(CRIT_4) * fourth),
) for group_name, first, second, third, fourth in result)
# Sort list in descending order of score
final_result = sorted(actual_result, key=lambda x: x[1], reverse=True)
self.Session.remove()
return json.dumps(final_result, default=alchemyencoder)
def get_compare_avg_cpu_load_data_for_test_ids(self, test_id_1, test_id_2):
test_1_name = self.execute_statement(
select([tests.c.display_name]).where(tests.c.id == test_id_1),
False)[0][0]
test_2_name = self.execute_statement(
select([tests.c.display_name]).where(tests.c.id == test_id_2),
False)[0][0]
if test_1_name == test_2_name:
test_1_name += '_1'
test_2_name += '_2'
st1 = select([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_1'),
literal(0).label('CPU_LOAD_2'),
])\
.where(tests_monitoring_data.c.test_id == tests.c.id)\
.where(tests.c.id == test_id_1).group_by(tests_monitoring_data.c.server_name)
st2 = select([
tests_monitoring_data.c.server_name,
literal(0).label('CPU_LOAD_1'),
func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD_2')]) \
.where(tests_monitoring_data.c.test_id == tests.c.id) \
.where(tests.c.id == test_id_2).group_by(tests_monitoring_data.c.server_name)
#s1 = st1.alias('s1')
#s2 = st2.alias('s2')
qt = union_all(st1, st2).alias("united")
qr = select([
qt.c.server_name,
func.sum(qt.c.CPU_LOAD_1).label(test_1_name),
func.sum(qt.c.CPU_LOAD_2).label(test_2_name)
]).group_by(qt.c.server_name)
#statement = select([s1.c.server_name,s1.c.CPU_LOAD.label(test_1_name),s2.c.CPU_LOAD.label(test_2_name)])\
# .where(s1.c.server_name==s2.c.server_name)
return self.execute_statement(qr, True)
def index():
avg = db.session.query(func.avg(Thing.count)).first()[0]
print("AVG: ", avg)
entries = Thing.query.filter(Thing.count <= avg).order_by(func.random()).limit(2).all()
for e in entries:
print("Count: ", e.count)
e.choosen()
db.session.add(e)
db.session.commit()
return render_template("index.html", left=entries[0], right=entries[1])
def update_avg_last_visited_dates(db: Session, fqdn_list):
for fqdn in fqdn_list:
avg_last_visited_date = (db.query(
func.to_timestamp(
func.avg(func.extract(
"epoch", db_models.Url.url_last_visited)))).filter(
db_models.Url.fqdn == fqdn.fqdn).scalar())
db.query(db_models.Frontier).filter(
db_models.Frontier.fqdn == fqdn.fqdn).update(
{"fqdn_avg_last_visited_date": avg_last_visited_date})
db.commit()
def calc_temps(start_date, end_date):
"""TMIN, TAVG, and TMAX for a list of dates.
Args:
start_date (string): A date string in the format %Y-%m-%d
end_date (string): A date string in the format %Y-%m-%d
Returns:
TMIN, TAVE, and TMAX
"""
return db.session.query(func.min(Hawaii.tobs), func.avg(Hawaii.tobs), func.max(Hawaii.tobs)).\
filter(Hawaii.date >= start_date).filter(Hawaii.date <= end_date).all()
def get_mean_precipitation_by_city(self, initial_date, final_date):
return db.session.query(City).with_entities(
City.id.label('city_id'),
City.name.label('city'),
State.abbreviation.label('state'),
func.avg(Forecast.rain_precipitation).label('mean')
) \
.join(State) \
.join(Forecast) \
.filter(Forecast.date >= initial_date) \
.filter(Forecast.date <= final_date) \
.group_by(City.id) \
.order_by(desc('mean')).all()
def get_ave_item_rating_data(self, db_session, item, webapp_model=None):
""" Returns the average rating for an item."""
if webapp_model is None:
webapp_model = galaxy.model
item_rating_assoc_class = self._get_item_rating_assoc_class(item, webapp_model=webapp_model)
if not item_rating_assoc_class:
raise RuntimeException("Item does not have ratings: %s" % item.__class__.__name__)
item_id_filter = self._get_item_id_filter_str(item, item_rating_assoc_class)
ave_rating = db_session.query(func.avg(item_rating_assoc_class.rating)).filter(item_id_filter).scalar()
# Convert ave_rating to float; note: if there are no item ratings, ave rating is None.
if ave_rating:
ave_rating = float(ave_rating)
else:
ave_rating = 0
num_ratings = int(db_session.query(func.count(item_rating_assoc_class.rating)).filter(item_id_filter).scalar())
return (ave_rating, num_ratings)
def get_ave_item_rating_data( self, db_session, item, webapp_model=None ):
""" Returns the average rating for an item."""
if webapp_model is None:
webapp_model = galaxy.model
item_rating_assoc_class = self._get_item_rating_assoc_class( item, webapp_model=webapp_model )
if not item_rating_assoc_class:
raise RuntimeException( "Item does not have ratings: %s" % item.__class__.__name__ )
item_id_filter = self._get_item_id_filter_str( item, item_rating_assoc_class )
ave_rating = db_session.query( func.avg( item_rating_assoc_class.rating ) ).filter( item_id_filter ).scalar()
# Convert ave_rating to float; note: if there are no item ratings, ave rating is None.
if ave_rating:
ave_rating = float( ave_rating )
else:
ave_rating = 0
num_ratings = int( db_session.query( func.count( item_rating_assoc_class.rating ) ).filter( item_id_filter ).scalar() )
return ( ave_rating, num_ratings )
def success():
if request.method == "POST":
email, height = request.form.values()
# check if user already exists
if db.session.query(Data).filter(Data.email == email).count() == 0:
data = Data(email, height)
db.session.add(data)
db.session.commit()
avg = db.session.query(func.avg(Data.height)).scalar()
avg = round(avg, 1)
count = db.session.query(Data.height).count()
send_email(email, height, avg, count)
return render_template("success.html")
return render_template("index.html",
text="This email already submitted a height!")
def testName(self):
date1 = datetime.date(2009, 5, 23)
date2 = datetime.date(2010, 5, 23)
date3 = datetime.date(2011, 5, 23)
date4 = datetime.date(2013, 5, 23)
date5 = datetime.date(2014, 5, 23)
with self.control._session_context as session:
deal = model.Deal()
session.add(deal)
deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date1))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date2))
deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date4))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(25), settlement_date = date4))
deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(75), settlement_date = date5))
deal.holdings.append(model.Holding(current_value=Decimal(10)))
deal.holdings.append(model.Holding(current_value=Decimal(-5)))
session.commit()
s = session.query(func.sum(model.Cashflow.amount), func.avg(model.Cashflow.amount))
s = s.filter(model.Cashflow.amount == Decimal(15))
print s
#s.filter(model.Cashflow.amount == Decimal(15))
#print s
r = session.execute(s)
id = deal.id
cash_flows = []
#for row in r:
#print row
back = session.query(model.Cashflow.settlement_date.label("settlement_date"),model.Cashflow.amount.label("amount")).\
filter(model.Cashflow.deal_id==deal.id)
forward = session.query(model.ForwardCashflow.settlement_date.label("settlement_date"), model.ForwardCashflow.amount.label("amount")).\
filter(model.ForwardCashflow.deal_id==deal.id)
cash = back.union_all(forward).order_by(literal_column("settlement_date").asc(), literal_column("amount").asc())
for c in cash:
print c
cash_flows.append(c)
irr = IRRCalculator(Decimal('0.10'), cash_flows)
irr2 = IRRCalculator(Decimal('0.10'), [])
#print irr.theIRR
#print irr2.theIRR
mm = MMCalculator(cash_flows)
print mm.theMM
print mm.errors
def _top_rated(session, limit, offset=9.90, appendto=tuple()):
# select movie_id, avg(rating) from rating group by movie_id
# having (avg(rating) > 9.90 and avg(rating) <=10.0);
top_offset = min([offset + .1, 10.0])
avg_ = func.avg(Rating.rating)
cnt_ = func.count(Rating.user_id)
res = session.query(Rating.movie_id, avg_)\
.group_by(Rating.movie_id)\
.having(and_(cnt_ > 1, avg_ > offset, avg_ <= top_offset))\
.all()
if len(res) > 0 or offset < 1.0:
res = tuple(res)
appendto = appendto + res
if len(appendto) >= limit or offset < 1.0:
appendto = list(appendto)
shuffle(appendto)
if len(appendto) > limit:
appendto[limit:] = []
return appendto
return Rating._top_rated(session, limit=limit, offset=offset-0.1, appendto=appendto)
def show(self, fullname, crew):
self.view['fullname'] = fullname
sql = """
select id from movie
where meta is not null
and btrim(json_select(meta, '{"%s"}')::text, '"') like :fullname """
if crew == 'director':
sql = sql % 'Director'
elif crew == 'screenwriter':
sql = sql % 'Writer'
elif crew == 'actor':
sql = sql % 'Actors'
else:
raise RuntimeError, 'routing for crew:%s not provided' % crew
fullname_ = fullname.encode('ascii', 'replace').replace('?','%')
movie_ids = self.session.execute(sql, {'fullname': '%'+fullname_+'%'}).fetchall()
movie_ids = map(itemgetter0, movie_ids)
sq = self.session.query(Rating.movie_id,
func.avg(Rating.rating).label('avg_rating'),
func.count(Rating.user_id).label('rev_cnt'))\
.group_by(Rating.movie_id).subquery()
movies = self.session.query(Movie, sq.c.avg_rating, sq.c.rev_cnt)\
.outerjoin((sq, sq.c.movie_id==Movie.id))\
.filter(Movie.id.in_(movie_ids))\
.order_by(Movie.year.desc()).all()
self.view.update({'crew': crew,
'movies': movies})
self.template = 'cast.phtml'
def ratings_avg( self, item ):
"""Returns the average of all ratings given to this item."""
foreign_key = self._foreign_key( self.rating_assoc )
avg = self.session().query( func.avg( self.rating_assoc.rating ) ).filter( foreign_key == item ).scalar()
return avg or 0.0
# Extract UserInfo and ItemInfo
from fetch import session, Base, engine, Users, Record, UserInfo, ItemInfo
from sqlalchemy.sql.expression import func
import numpy as np
cnt=0;
for usr in session.query(Users.name).order_by(Users.uid).all():
count = session.query(Record).filter(Record.name==usr.name).count()
ratecount = session.query(Record).filter(Record.name==usr.name, Record.rate != None).count()
average = session.query(func.avg(Record.rate).label('average')).\
filter(Record.name==usr.name, Record.rate != None).scalar();
temp = [];
for q in session.query(Record.rate).filter(Record.name==usr.name, Record.rate != None):
temp.append(q.rate)
sd = np.std(temp)
if count>0:
if ratecount>0:
itm = UserInfo(name=usr.name, index=cnt, count=count, ratecount=ratecount, \
average = average, sd=sd)
else:
itm = UserInfo(name=usr.name, index=cnt, count=count, ratecount=ratecount)
session.add(itm)
cnt+=1
else:
itm = UserInfo(name=usr.name, count=0, ratecount=0)
session.add(itm)
session.commit()
nUsers=cnt+1
cnt=0;
for rec in session.query(Record.iid).group_by(Record.iid).order_by(Record.iid).all():
def get_integration_asmt_score_avgs(self):
with get_udl_connection() as conn:
int_outcome = conn.get_table('int_sbac_asmt_outcome')
query = select([func.avg(int_outcome.c.score_asmt),
func.avg(int_outcome.c.score_asmt_min),
func.avg(int_outcome.c.score_asmt_max),
func.avg(int_outcome.c.score_claim_1),
func.avg(int_outcome.c.score_claim_1_min),
func.avg(int_outcome.c.score_claim_1_max),
func.avg(int_outcome.c.score_claim_2),
func.avg(int_outcome.c.score_claim_2_min),
func.avg(int_outcome.c.score_claim_2_max),
func.avg(int_outcome.c.score_claim_3),
func.avg(int_outcome.c.score_claim_3_min),
func.avg(int_outcome.c.score_claim_3_max),
func.avg(int_outcome.c.score_claim_4),
func.avg(int_outcome.c.score_claim_4_min),
func.avg(int_outcome.c.score_claim_4_max)], from_obj=int_outcome)
result = conn.execute(query)
for row in result:
asmt_avgs = row
return asmt_avgs
def edit_review(self, trans, **kwd):
# The value of the received id is the encoded review id.
message = escape(kwd.get('message', ''))
status = kwd.get('status', 'done')
review_id = kwd.get('id', None)
review = review_util.get_review(trans.app, review_id)
components_dict = odict()
for component in review_util.get_components(trans.app):
components_dict[component.name] = dict(component=component,
component_review=None)
repository = review.repository
repo = hg_util.get_repo_for_repository(trans.app,
repository=repository,
repo_path=None,
create=False)
for component_review in review.component_reviews:
if component_review and component_review.component:
component_name = component_review.component.name
if component_name in components_dict:
component_review_dict = components_dict[component_name]
component_review_dict[
'component_review'] = component_review
components_dict[component_name] = component_review_dict
# Handle a Save button click.
save_button_clicked = False
save_buttons = [
'%s%sreview_button' % (component_name, STRSEP)
for component_name in components_dict.keys()
]
save_buttons.append('revision_approved_button')
for save_button in save_buttons:
if save_button in kwd:
save_button_clicked = True
break
if save_button_clicked:
# Handle the revision_approved_select_field value.
revision_approved = kwd.get('revision_approved', None)
revision_approved_setting_changed = False
if revision_approved:
revision_approved = str(revision_approved)
if review.approved != revision_approved:
revision_approved_setting_changed = True
review.approved = revision_approved
trans.sa_session.add(review)
trans.sa_session.flush()
saved_component_names = []
for component_name in components_dict.keys():
flushed = False
# Retrieve the review information from the form.
# The star rating form field is a radio button list, so it will not be received if it was not clicked in the form.
# Due to this behavior, default the value to 0.
rating = 0
for k, v in kwd.items():
if k.startswith('%s%s' % (component_name, STRSEP)):
component_review_attr = k.replace(
'%s%s' % (component_name, STRSEP), '')
if component_review_attr == 'component_id':
component_id = str(v)
elif component_review_attr == 'comment':
comment = str(v)
elif component_review_attr == 'private':
private = CheckboxField.is_checked(v)
elif component_review_attr == 'approved':
approved = str(v)
elif component_review_attr == 'rating':
rating = int(str(v))
component = review_util.get_component(trans.app, component_id)
component_review = \
review_util.get_component_review_by_repository_review_id_component_id( trans.app,
review_id,
component_id )
if component_review:
# See if the existing component review should be updated.
if component_review.comment != comment or \
component_review.private != private or \
component_review.approved != approved or \
component_review.rating != rating:
component_review.comment = comment
component_review.private = private
component_review.approved = approved
component_review.rating = rating
trans.sa_session.add(component_review)
trans.sa_session.flush()
flushed = True
saved_component_names.append(component_name)
else:
# See if a new component_review should be created.
if comment or private or approved != trans.model.ComponentReview.approved_states.NO or rating:
component_review = trans.model.ComponentReview(
repository_review_id=review.id,
component_id=component.id,
comment=comment,
approved=approved,
rating=rating)
trans.sa_session.add(component_review)
trans.sa_session.flush()
flushed = True
saved_component_names.append(component_name)
if flushed:
# Update the repository rating value to be the average of all component review ratings.
average_rating = trans.sa_session.query( func.avg( trans.model.ComponentReview.table.c.rating ) ) \
.filter( and_( trans.model.ComponentReview.table.c.repository_review_id == review.id,
trans.model.ComponentReview.table.c.deleted == False,
trans.model.ComponentReview.table.c.approved != trans.model.ComponentReview.approved_states.NA ) ) \
.scalar()
if average_rating is not None:
review.rating = int(average_rating)
trans.sa_session.add(review)
trans.sa_session.flush()
# Update the information in components_dict.
if component_name in components_dict:
component_review_dict = components_dict[component_name]
component_review_dict[
'component_review'] = component_review
components_dict[component_name] = component_review_dict
if revision_approved_setting_changed:
message += 'Approved value <b>%s</b> saved for this revision.<br/>' % review.approved
if saved_component_names:
message += 'Reviews were saved for components: %s' % ', '.join(
saved_component_names)
if not revision_approved_setting_changed and not saved_component_names:
message += 'No changes were made to this review, so nothing was saved.'
if review and review.approved:
selected_value = review.approved
else:
selected_value = trans.model.ComponentReview.approved_states.NO
revision_approved_select_field = grids_util.build_approved_select_field(
trans,
name='revision_approved',
selected_value=selected_value,
for_component=False)
rev, changeset_revision_label = hg_util.get_rev_label_from_changeset_revision(
repo, review.changeset_revision)
return trans.fill_template(
'/webapps/tool_shed/repository_review/edit_review.mako',
repository=repository,
review=review,
changeset_revision_label=changeset_revision_label,
revision_approved_select_field=revision_approved_select_field,
components_dict=components_dict,
message=message,
status=status)
def get_staging_asmt_score_avgs(self):
with get_udl_connection() as conn:
stg_outcome = conn.get_table('stg_sbac_asmt_outcome')
query = select([func.avg(cast(stg_outcome.c.assessmentsubtestresultscorevalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestminimumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestmaximumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestresultscoreclaim1value, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim1minimumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim1maximumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestresultscoreclaim2value, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim2minimumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim2maximumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestresultscoreclaim3value, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim3minimumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim3maximumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestresultscoreclaim4value, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim4minimumvalue, Integer)),
func.avg(cast(stg_outcome.c.assessmentsubtestclaim4maximumvalue, Integer))], from_obj=stg_outcome)
result = conn.execute(query)
for row in result:
asmt_avgs = row
return asmt_avgs
def get_edware_asmt_score_avgs(self, tenant, schema):
with get_target_connection(tenant, schema) as conn:
fact = conn.get_table('fact_asmt_outcome_vw')
query = select([func.avg(fact.c.asmt_score),
func.avg(fact.c.asmt_score_range_min),
func.avg(fact.c.asmt_score_range_max),
func.avg(fact.c.asmt_claim_1_score),
func.avg(fact.c.asmt_claim_1_score_range_min),
func.avg(fact.c.asmt_claim_1_score_range_max),
func.avg(fact.c.asmt_claim_2_score),
func.avg(fact.c.asmt_claim_2_score_range_min),
func.avg(fact.c.asmt_claim_2_score_range_max),
func.avg(fact.c.asmt_claim_3_score),
func.avg(fact.c.asmt_claim_3_score_range_min),
func.avg(fact.c.asmt_claim_3_score_range_max),
func.avg(fact.c.asmt_claim_4_score),
func.avg(fact.c.asmt_claim_4_score_range_min),
func.avg(fact.c.asmt_claim_4_score_range_max)], from_obj=fact)
result = conn.execute(query)
for row in result:
star_asmt_avgs = row
return star_asmt_avgs
def ratings_avg(self, item):
"""Returns the average of all ratings given to this item."""
foreign_key = self._foreign_key(self.rating_assoc)
avg = self.session().query(func.avg(self.rating_assoc.rating)).filter(foreign_key == item).scalar()
return avg or 0.0
def getFactions(self, **query):
rst = (models.session.query(PlayerModel).with_entities(
PlayerModel.faction, func.count(PlayerModel.faction),
func.avg(PlayerModel.rank),
func.max(PlayerModel.lastActive)).filter(
PlayerModel.faction != None,
PlayerModel.faction != '').group_by(PlayerModel.faction))
if 'name' in query:
rst = rst.filter_by(faction=query['name'])
if 'search' in query:
rst = rst.filter(
PlayerModel.faction.ilike(_single(query['search'])))
if 'minplayers' in query:
try:
rst = rst.having(
func.count(PlayerModel.faction) >= int(query['minplayers'])
)
except ValueError as e:
logging.warn("IstroAPI: %s", e)
rst = rst.where(False)
if 'order' in query:
order = _single(query['order'])
if order == 'playercount_des':
rst = rst.order_by(func.count(PlayerModel.faction).desc())
elif order == 'playercount_asc':
rst = rst.order_by(func.count(PlayerModel.faction).asc())
elif order == 'name_des':
rst = rst.order_by(PlayerModel.faction.desc())
elif order == 'name_asc':
rst = rst.order_by(PlayerModel.faction.asc())
elif order == 'rank_des':
rst = rst.order_by(func.avg(PlayerModel.rank).desc())
elif order == 'rank_asc':
rst = rst.order_by(func.avg(PlayerModel.rank).asc())
elif order == 'active_des':
rst = rst.order_by(
func.max(PlayerModel.lastActive).desc().nullslast())
elif order == 'active_asc':
rst = rst.order_by(
func.max(PlayerModel.lastActive).asc().nullslast())
count = rst.count()
rst = rst.offset(_single(query.get('offset', 0)))
limit = int(_single(query.get('limit', 50)))
rst = rst.limit(min(limit, 500))
return {
'count':
count,
'factions': [{
'name': r[0],
'size': r[1],
'rank': r[2],
'lastActive': r[3]
} for r in rst]
}
def return_avg_goals_per_team_pergame_desc():
return session.query(Team.country, func.avg(
Statistics.goals)).group_by(Statistics.name).order_by(
func.avg(Statistics.goals).desc()).join(Statistics).all()
def average_dc_vs_pa_per_game():
return session.query(
Team.country, func.avg(Statistics.distance_covered),
func.avg(Statistics.pass_accuracy)).join(Statistics).group_by(
Team.country).order_by(
func.avg(Statistics.distance_covered).asc()).all()
userAvg=dict()
for usr in session.query(UserInfo.name, UserInfo.index, UserInfo.average).filter(UserInfo.index != None).all():
tableUI[usr.name]=usr.index
for rec in session.query(ItemInfo.i_index, ItemInfo.index).all():
tableII[rec.i_index]=rec.index
#nUsers=session.query(UserInfo).filter(UserInfo.index!=None).count()
nItms=session.query(ItemInfo).count()
gp=dict()
gp['bias_states']={}
global_avg=session.query(func.avg(Record.rate).label('average')).filter(Record.rate!=None).scalar();
for q in session.query(Record.state, func.avg(Record.rate).label('average')).\
filter(Record.rate != None).group_by(Record.state):
gp['bias_states'][q.state]=float(q.average)-float(global_avg)
nFaved = session.query(Record).count()
data = np.zeros(nFaved)
i = np.zeros(nFaved)
j = np.zeros(nFaved)
idx=0
for q in session.query(Record.name, Record.iid, Record.state, Record.rate):
p = session.query(UserInfo.name, UserInfo.sd, UserInfo.ratecount, UserInfo.average).filter(UserInfo.name==q.name).first()
try:
if q.rate!=None and p.ratecount>3 and p.sd>0.1:
data[idx]=q.rate-p.average
def edit_review( self, trans, **kwd ):
# The value of the received id is the encoded review id.
message = kwd.get( 'message', '' )
status = kwd.get( 'status', 'done' )
review_id = kwd.get( 'id', None )
review = review_util.get_review( trans.app, review_id )
components_dict = odict()
for component in review_util.get_components( trans.app ):
components_dict[ component.name ] = dict( component=component, component_review=None )
repository = review.repository
repo = hg_util.get_repo_for_repository( trans.app, repository=repository, repo_path=None, create=False )
for component_review in review.component_reviews:
if component_review and component_review.component:
component_name = component_review.component.name
if component_name in components_dict:
component_review_dict = components_dict[ component_name ]
component_review_dict[ 'component_review' ] = component_review
components_dict[ component_name ] = component_review_dict
# Handle a Save button click.
save_button_clicked = False
save_buttons = [ '%s%sreview_button' % ( component_name, STRSEP ) for component_name in components_dict.keys() ]
save_buttons.append( 'revision_approved_button' )
for save_button in save_buttons:
if save_button in kwd:
save_button_clicked = True
break
if save_button_clicked:
# Handle the revision_approved_select_field value.
revision_approved = kwd.get( 'revision_approved', None )
revision_approved_setting_changed = False
if revision_approved:
revision_approved = str( revision_approved )
if review.approved != revision_approved:
revision_approved_setting_changed = True
review.approved = revision_approved
trans.sa_session.add( review )
trans.sa_session.flush()
saved_component_names = []
for component_name in components_dict.keys():
flushed = False
# Retrieve the review information from the form.
# The star rating form field is a radio button list, so it will not be received if it was not clicked in the form.
# Due to this behavior, default the value to 0.
rating = 0
for k, v in kwd.items():
if k.startswith( '%s%s' % ( component_name, STRSEP ) ):
component_review_attr = k.replace( '%s%s' % ( component_name, STRSEP ), '' )
if component_review_attr == 'component_id':
component_id = str( v )
elif component_review_attr == 'comment':
comment = str( v )
elif component_review_attr == 'private':
private = CheckboxField.is_checked( v )
elif component_review_attr == 'approved':
approved = str( v )
elif component_review_attr == 'rating':
rating = int( str( v ) )
component = review_util.get_component( trans.app, component_id )
component_review = \
review_util.get_component_review_by_repository_review_id_component_id( trans.app,
review_id,
component_id )
if component_review:
# See if the existing component review should be updated.
if component_review.comment != comment or \
component_review.private != private or \
component_review.approved != approved or \
component_review.rating != rating:
component_review.comment = comment
component_review.private = private
component_review.approved = approved
component_review.rating = rating
trans.sa_session.add( component_review )
trans.sa_session.flush()
flushed = True
saved_component_names.append( component_name )
else:
# See if a new component_review should be created.
if comment or private or approved != trans.model.ComponentReview.approved_states.NO or rating:
component_review = trans.model.ComponentReview( repository_review_id=review.id,
component_id=component.id,
comment=comment,
approved=approved,
rating=rating )
trans.sa_session.add( component_review )
trans.sa_session.flush()
flushed = True
saved_component_names.append( component_name )
if flushed:
# Update the repository rating value to be the average of all component review ratings.
average_rating = trans.sa_session.query( func.avg( trans.model.ComponentReview.table.c.rating ) ) \
.filter( and_( trans.model.ComponentReview.table.c.repository_review_id == review.id,
trans.model.ComponentReview.table.c.deleted == False,
trans.model.ComponentReview.table.c.approved != trans.model.ComponentReview.approved_states.NA ) ) \
.scalar()
if average_rating is not None:
review.rating = int( average_rating )
trans.sa_session.add( review )
trans.sa_session.flush()
# Update the information in components_dict.
if component_name in components_dict:
component_review_dict = components_dict[ component_name ]
component_review_dict[ 'component_review' ] = component_review
components_dict[ component_name ] = component_review_dict
if revision_approved_setting_changed:
message += 'Approved value <b>%s</b> saved for this revision.<br/>' % review.approved
if saved_component_names:
message += 'Reviews were saved for components: %s' % ', '.join( saved_component_names )
if not revision_approved_setting_changed and not saved_component_names:
message += 'No changes were made to this review, so nothing was saved.'
if review and review.approved:
selected_value = review.approved
else:
selected_value = trans.model.ComponentReview.approved_states.NO
revision_approved_select_field = grids_util.build_approved_select_field( trans,
name='revision_approved',
selected_value=selected_value,
for_component=False )
rev, changeset_revision_label = hg_util.get_rev_label_from_changeset_revision( repo, review.changeset_revision )
return trans.fill_template( '/webapps/tool_shed/repository_review/edit_review.mako',
repository=repository,
review=review,
changeset_revision_label=changeset_revision_label,
revision_approved_select_field=revision_approved_select_field,
components_dict=components_dict,
message=message,
status=status )
def average_dc_vs_bp_per_game():
return session.query(
Team.country, func.avg(Statistics.distance_covered),
func.avg(Statistics.ball_possession)).join(Statistics).group_by(
Team.country).order_by(
func.avg(Statistics.distance_covered).asc()).all()
def get_analysis_data(parent_id):
"""
Provides analysis of average thickness and average thinning of given
parent node's children nodes and gives predictions
for the next 2 years based on analysis
:param parent_id:
:return: dict
"""
result = {
"avg_thickness": 0,
"avg_thinning": 0,
"last_year": 0,
"stacked_bar": {
"labels": [],
"thickness": [],
"thinning": []
},
"pie": {}
}
prev_measurements = aliased(Measurement)
year = func.extract('year', Measurement.measure_date).label("year")
prev_year = func.extract('year', prev_measurements.measure_date).\
label("prev_year")
current_avg = func.avg(Measurement.value).label("avg_thick")
prev_avg = func.avg(prev_measurements.value).label("prev_avg_thick")
# get years, avg thickness and avg thinning
thicknesses = db.session.query(year, current_avg,
(prev_avg - current_avg).label("diff")). \
join(Node, Node.id == Measurement.node_id). \
outerjoin(prev_measurements,
and_(Measurement.node_id == prev_measurements.node_id,
year == prev_year + 1)). \
filter(Node.parent_id == parent_id).group_by(year, prev_year). \
order_by(year, prev_year)
if len(thicknesses.all()) > 4:
thicknesses = thicknesses.all()[-4:]
else:
thicknesses = thicknesses.all()
# calculations for stacked bar
labels = []
thickness = []
thinning = []
for year, avg_thickness, diff_thinning in thicknesses:
labels.append(int(year))
thickness.append(round(avg_thickness, 2))
thinning.append(
abs(round(diff_thinning, 2)) if diff_thinning is not None else 0)
# get average thickness
result["avg_thickness"] = thickness[-1]
# get average thinning
avg_thinning = round((sum(thinning) / len(thinning)), 2)
result["avg_thinning"] = avg_thinning
# get last year of measurements
result["last_year"] = labels[-1]
# add predictions for next 2 years
for i in range(2):
labels.append(labels[-1] + 1)
thickness.append(thickness[-1] - avg_thinning)
thinning.append(avg_thinning)
result["stacked_bar"]["labels"] = labels
result["stacked_bar"]["thickness"] = thickness
result["stacked_bar"]["thinning"] = thinning
# get data for pie
# compare value with norms and define it to one of 4 groups
category = case([(and_(Measurement.value > Norm.minor,
Measurement.value <= Norm.default), 0),
(and_(Measurement.value > Norm.major,
Measurement.value <= Norm.minor), 1),
(and_(Measurement.value > Norm.defect,
Measurement.value <= Norm.major), 2)],
else_=3).label("category")
# get data
year = func.extract('year', Measurement.measure_date).label("year")
pie_data = db.session.query(year, category, func.count(1).label("cnt")). \
join(Node, Node.id == Measurement.node_id). \
join(Norm, Norm.node_id == Measurement.node_id). \
filter(Node.parent_id == parent_id).group_by(year, category)
# calculations
pie = {}
for year, cat, num in pie_data.all():
year = str(int(year))
if year not in pie:
pie[year] = [0, 0, 0, 0]
pie[year][cat] = num
result["pie"] = pie
return result
