def reliability(zone):
if zone == 'sf':
data = data = Realtime.select(
Realtime.agency, Realtime.mode,
fn.AVG(Realtime.otp).alias('otp'), fn.DATE(Realtime.timestamp).alias('timestamp')
).where(
Realtime.region == zone,
Realtime.otp > 0,
Realtime.agency.in_(['AC TRANSIT', 'Bay Area Rapid Transit', 'San Francisco Municipal Transportation Agency'])
).group_by(Realtime.agency, Realtime.mode, fn.DATE(Realtime.timestamp))
data = [model_to_dict(r) for r in data]
else:
data = Realtime.select(
Realtime.agency, Realtime.mode,
fn.AVG(Realtime.otp).alias('otp'), fn.DATE(Realtime.timestamp).alias('timestamp')
).where(
Realtime.region == zone,
Realtime.otp > 0
).group_by(Realtime.agency, Realtime.mode, fn.DATE(Realtime.timestamp))
data = [model_to_dict(r) for r in data]
for entry in data:
del(entry['region'])
del(entry['delay_abs'])
del(entry['delay_late'])
del(entry['delay_early'])
del(entry['fraction'])
del(entry['id'])
return jsonify(data)
def get_avg_indexer_response_times(after, before):
result = []
response_times = []
for p in Indexer.select().order_by(Indexer.name):
try:
indexer = getIndexerByName(p.name)
if not indexer.settings.enabled:
logger.debug("Skipping download stats for %s" % p.name)
continue
except IndexerNotFoundException:
logger.error("Unable to find indexer %s in configuration" % p.name)
continue
where = (IndexerApiAccess.response_successful) & (
IndexerApiAccess.indexer == p
) & (IndexerApiAccess.time > after) & (IndexerApiAccess.time < before)
avg_response_time = IndexerApiAccess().select(
fn.AVG(IndexerApiAccess.response_time)).where(where).tuples()[0][0]
if avg_response_time:
response_times.append({
"name": p.name,
"avgResponseTime": int(avg_response_time)
})
where = (IndexerApiAccess.response_successful) & (
IndexerApiAccess.response_time is not None) & (
IndexerApiAccess.time > after) & (IndexerApiAccess.time < before)
avg_response_time = IndexerApiAccess().select(
fn.AVG(IndexerApiAccess.response_time)).where(where).tuples()[0][0]
for i in response_times:
delta = i["avgResponseTime"] - avg_response_time
i["delta"] = delta
result.append(i)
result = sorted(result, key=lambda x: x["name"])
result = sorted(result, key=lambda x: x["avgResponseTime"])
return result
def get(self):
args = self.reqparse.parse_args()
expression = ((Product.species_id == args['species_id']) &
(Product.date >= args['from']) &
(Product.date <= args['to']))
# clean unecessary args
del args['species_id']
del args['from']
del args['to']
# build where clause based on qeury params
for key, value in args.items():
if value is not None:
expression &= (getattr(Product, key) == value)
try:
products = [
marshal(product, product_fields) for product in (
Product.select(Product.date,
fn.avg(Product.min).alias('min'),
fn.AVG(Product.max).alias('max'),
fn.AVG(Product.mean).alias('mean')).
where(expression).group_by(Product.date).order_by(
Product.date).dicts())
]
except Exception as e:
print(e)
abort(400, message="Something went wrong")
else:
return {'products': products}
def average_age(self):
if self.age == "all":
querry = Person.select(fn.AVG(Person.dob_age))
else:
querry = Person.select(fn.AVG(Person.dob_age)).where(
Person.gender == self.age
)
return querry.scalar()
def get_avg_age(gender):
if gender == 'all':
res = Person.select(fn.AVG(Person.dob_age).alias('avg_age')).get()
return res.avg_age
else:
res = Person.select(fn.AVG(Person.dob_age).alias('avg_age')) \
.where(Person.gender == gender).get()
return res.avg_age
def show_average(self,gender):
# print average age
if gender == "all":
query = Person.select(fn.AVG(Person.age)).scalar()
elif gender == 'female':
query = Person.select(fn.AVG(Person.age)).where(Person.gender == "female").scalar()
elif gender == "male":
query = Person.select(fn.AVG(Person.age)).where(Person.gender == "male").scalar()
print(query)
def average_value(self, table, column, condition=None, cond_value=None):
"""Calculate the average value of the selected column."""
cls = getattr(import_module(self.__models), table)
attr = getattr(cls, column)
with self.__db:
if condition:
cond_attr = getattr(cls, condition)
return cls.select(
fn.AVG(attr).alias('avg')).where(cond_attr == cond_value)
else:
return cls.select(fn.AVG(attr).alias('avg'))
def get_metrics(registration_id):
# Collect and return the following metrics:
# - Current number of containers running for this registration
# - Average CPU usage for this registration
# - Average memory usage for this registration
# - Average processing time for this registration
# - Number of events per day for the last week
result = {}
registration = Registration.get(Registration.id == registration_id)
stats = Metric.select(
Metric.name,
fn.AVG(Metric.value).alias('average')).where(
Metric.topic == registration.topic,
Metric.container == registration.container).group_by(Metric.name)
# Add to the result object
for stat in stats:
result[stat.name] = stat.average
# Use the beginning of the day as a starting point (for full
# days)
today_dt = datetime.datetime.combine(datetime.date.today(),
datetime.time.min)
today = int(time.mktime(today_dt.timetuple()) * 1000)
# Get the starting point (one week ago)
one_week_ago = today - (7 * MILLISECONDS_IN_DAY)
events = Event.select(
# In order to query by date intervals, we need to divide by one day
# worth of milliseconds, round to the nearest whole number, then
# multiply the milliseconds back in. This will create intervals and
# normalize the returned values.
(fn.ROUND(Event.timestamp / MILLISECONDS_IN_DAY) * MILLISECONDS_IN_DAY
).alias('date'),
fn.COUNT(Event.id).alias('events')).where(
Event.topic == registration.topic,
Event.container == registration.container,
Event.timestamp >= one_week_ago).group_by('date')
# Add to the result object
result['events'] = {}
for event in events:
result['events'][int(event.date)] = event.events
avg_duration = Event.select(fn.AVG(Event.duration)).where(
Event.topic == registration.topic,
Event.container == registration.container).scalar()
result['duration'] = avg_duration
result['containers'] = docker_agent.container_count(
registration.topic, registration.container)
return result
def query_nb201_trial_stats(arch, num_epochs, dataset, reduction=None, include_intermediates=False):
"""
Query trial stats of NAS-Bench-201 given conditions.
Parameters
----------
arch : dict or None
If a dict, it is in the format that is described in
:class:`nni.nas.benchmark.nasbench201.Nb201TrialConfig`. Only trial stats
matched will be returned. If none, architecture will be a wildcard.
num_epochs : int or None
If int, matching results will be returned. Otherwise a wildcard.
dataset : str or None
If specified, can be one of the dataset available in :class:`nni.nas.benchmark.nasbench201.Nb201TrialConfig`.
Otherwise a wildcard.
reduction : str or None
If 'none' or None, all trial stats will be returned directly.
If 'mean', fields in trial stats will be averaged given the same trial config.
include_intermediates : boolean
If true, intermediate results will be returned.
Returns
-------
generator of dict
A generator of :class:`nni.nas.benchmark.nasbench201.Nb201TrialStats` objects,
where each of them has been converted into a dict.
"""
fields = []
if reduction == 'none':
reduction = None
if reduction == 'mean':
for field_name in Nb201TrialStats._meta.sorted_field_names:
if field_name not in ['id', 'config', 'seed']:
fields.append(fn.AVG(getattr(Nb201TrialStats, field_name)).alias(field_name))
elif reduction is None:
fields.append(Nb201TrialStats)
else:
raise ValueError('Unsupported reduction: \'%s\'' % reduction)
query = Nb201TrialStats.select(*fields, Nb201TrialConfig).join(Nb201TrialConfig)
conditions = []
if arch is not None:
conditions.append(Nb201TrialConfig.arch == arch)
if num_epochs is not None:
conditions.append(Nb201TrialConfig.num_epochs == num_epochs)
if dataset is not None:
conditions.append(Nb201TrialConfig.dataset == dataset)
if conditions:
query = query.where(functools.reduce(lambda a, b: a & b, conditions))
if reduction is not None:
query = query.group_by(Nb201TrialStats.config)
for trial in query:
if include_intermediates:
data = model_to_dict(trial)
# exclude 'trial' from intermediates as it is already available in data
data['intermediates'] = [
{k: v for k, v in model_to_dict(t).items() if k != 'trial'} for t in trial.intermediates
]
yield data
else:
yield model_to_dict(trial)
def query_nb101_trial_stats(arch,
num_epochs,
isomorphism=True,
reduction=None):
"""
Query trial stats of NAS-Bench-101 given conditions.
Parameters
----------
arch : dict or None
If a dict, it is in the format that is described in
:class:`nni.nas.benchmark.nasbench101.Nb101TrialConfig`. Only trial stats
matched will be returned. If none, architecture will be a wildcard.
num_epochs : int or None
If int, matching results will be returned. Otherwise a wildcard.
isomorphism : boolean
Whether to match essentially-same architecture, i.e., architecture with the
same graph-invariant hash value.
reduction : str or None
If 'none' or None, all trial stats will be returned directly.
If 'mean', fields in trial stats will be averaged given the same trial config.
Returns
-------
generator of dict
A generator of :class:`nni.nas.benchmark.nasbench101.Nb101TrialStats` objects,
where each of them has been converted into a dict.
"""
fields = []
if reduction == 'none':
reduction = None
if reduction == 'mean':
for field_name in Nb101TrialStats._meta.sorted_field_names:
if field_name not in ['id', 'config']:
fields.append(
fn.AVG(getattr(Nb101TrialStats,
field_name)).alias(field_name))
elif reduction is None:
fields.append(Nb101TrialStats)
else:
raise ValueError('Unsupported reduction: \'%s\'' % reduction)
query = Nb101TrialStats.select(*fields,
Nb101TrialConfig).join(Nb101TrialConfig)
conditions = []
if arch is not None:
if isomorphism:
num_vertices = infer_num_vertices(arch)
conditions.append(
Nb101TrialConfig.hash == hash_module(arch, num_vertices))
else:
conditions.append(Nb101TrialConfig.arch == arch)
if num_epochs is not None:
conditions.append(Nb101TrialConfig.num_epochs == num_epochs)
if conditions:
query = query.where(functools.reduce(lambda a, b: a & b, conditions))
if reduction is not None:
query = query.group_by(Nb101TrialStats.config)
for k in query:
yield model_to_dict(k)
def avg() -> 'ModelSelect':
"""平均数.
SELECT AVG(prod_price) AS avg_price
FROM product;
"""
avg_price = fn.AVG(Product.prod_price).alias('avg_price')
return (Product.select(avg_price))
def get_avg(vectorName: str):
try:
return Vector.select(fn.AVG(Vector.vectorMax).alias("avg")).where(
Vector.vectorName % f"*{vectorName}*").get()
except Vector.DoesNotExist:
return None
except Exception as ex:
raise ex
def distinct_avg() -> 'ModelSelect':
"""聚集不同值.
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM product
WHERE vend_id = 'DLL01';
"""
avg_price = fn.AVG(Product.prod_price.distinct()).alias('avg_price')
return (Product.select(avg_price).where(Product.vend_id == 'DLL01'))
def api_stat_site_top_user_in_date_range(start, end, ttype):
tmap = {
'danmu': TOP_TYPE_DCOUNT,
'gift': TOP_TYPE_GCOUNT,
'expense': TOP_TYPE_EXPENSE
}
order_by = {
TOP_TYPE_DCOUNT: SQL('davg').desc(),
TOP_TYPE_GCOUNT: SQL('gavg').desc(),
TOP_TYPE_EXPENSE: SQL('eavg').desc(),
}
try:
start = datetime.strptime(start, '%Y-%m-%d').date()
end = datetime.strptime(end, '%Y-%m-%d').date()
limit = int(request.args.get('limit', 20))
limit = limit if limit > 0 else 20
ttype = tmap[ttype]
except ValueError or KeyError:
return jsonify({'code': 1, 'msg': 'invalid request'})
query = SiteDailyTopUser.select(
SiteDailyTopUser.user,
fn.AVG(SiteDailyTopUser.dcount).alias('davg'),
fn.AVG(SiteDailyTopUser.gcount).alias('gavg'),
fn.AVG(SiteDailyTopUser.expense).alias('eavg')
).join(Date, on=(SiteDailyTopUser.date == Date.date_key)) \
.where((Date.date >= start) & (Date.date <= end) & (SiteDailyTopUser.ttype == ttype)) \
.group_by(SiteDailyTopUser.user) \
.order_by(order_by[ttype]) \
.limit(limit)
payload = []
order = 0
for row in query:
payload.append({
'user': row.user.name,
'dcount': row.davg,
'gcount': row.gavg,
'expense': row.eavg,
})
order += 1
return jsonify({'code': 0, 'msg': 'success', 'data': payload})
def show_book_rates():
query = BookShelf.select(
BookShelf.book,
fn.AVG(BookShelf.rate).alias('rates_avg'), # wrong data
fn.SUM(BookShelf.rate).alias('rates_sum'),
fn.COUNT(BookShelf.rate).alias('rates_count'),
).group_by(BookShelf.book)
for q in query:
print(q.book.id, q.rates_avg, q.rates_sum / q.rates_count)
def donor_info(minimum=0, maximum=99999999999999999, factor=1):
info = (Donation
.select(Donation.donor,
fn.COUNT(Donation.value).alias('num_gift'),
fn.SUM(factor*Donation.value).alias('sum_gift'),
fn.AVG(factor*Donation.value).alias('avg_gift'))
.where(Donation.value >= minimum, Donation.value <= maximum)
.group_by(Donation.donor)
.order_by(Donation.donor))
return info
def stats(cls):
q = cls.select(Origin.code, Origin.title, Origin.tick_size,
fn.MIN(cls.ts).alias('min_ts'),
fn.MAX(cls.ts).alias('max_ts'),
fn.COUNT(cls.id).alias('num_marks'),
fn.MIN(cls.dts).alias('min_dts'),
fn.MAX(cls.dts).alias('max_dts'),
fn.AVG(cls.dts).alias('avg_dts'),
fn.STDDEV_POP(
cls.dts).alias('std_dts')).join(Origin).group_by(
Origin.code)
return q.namedtuples()
def get_avg_indexer_response_times():
result = []
response_times = []
for p in Indexer.select().order_by(Indexer.name):
avg_response_time = IndexerApiAccess().select(
fn.AVG(IndexerApiAccess.response_time)).where(
(IndexerApiAccess.response_successful)
& (IndexerApiAccess.indexer == p)).tuples()[0][0]
if avg_response_time:
response_times.append({
"name": p.name,
"avgResponseTime": avg_response_time
})
avg_response_time = IndexerApiAccess().select(
fn.AVG(IndexerApiAccess.response_time)).where(
(IndexerApiAccess.response_successful)
& (IndexerApiAccess.response_time is not None)).tuples()[0][0]
for i in response_times:
delta = i["avgResponseTime"] - avg_response_time
i["delta"] = delta
result.append(i)
return result
def multi_reduce() -> 'ModelSelect':
"""多个聚集函数.
SELECT COUNT(*) AS count,
MIN(prod_price) AS min,
MAX(prod_price) AS max,
AVG(prod_price) AS avg,
SUM(prod_price) AS sum
FROM product;
"""
return (Product.select(count().alias('count'),
fn.MIN(Product.prod_price).alias('min'),
fn.MAX(Product.prod_price).alias('max'),
fn.AVG(Product.prod_price).alias('avg'),
fn.SUM(Product.prod_price).alias('sum')))
def best_average_rating() -> models.Restaurant:
"""You want to know what restaurant is best
Query the database to retrieve the restaurant that has the highest
rating on average
"""
average = (models.Rating
.select(fn.AVG(models.Rating.rating))
)
bst_rst = (models.Restaurant
.select()
.join(Rating)
.order_by(average)
.limit(1)
)
return bst_rst
def list_by_total(self):
"""Returns a list of donors sorted by total donations"""
try:
self.database.connect()
ord = (Donor.select(
Donor.name,
fn.SUM(Donation.donation_amount).alias('total_donations'),
fn.COUNT(Donation.donation_amount).alias('count'),
(fn.AVG(Donation.donation_amount).alias('avg')
)).join(Donation).group_by(Donor.name).order_by(
fn.SUM(Donation.donation_amount).desc()))
return tuple([(d.name, d.total_donations, d.count, float(d.avg))
for d in ord])
except Exception as e:
log.debug(e)
finally:
self.database.close()
def get_month_metrics_for_node(my_id, target_id, start_date, end_date) -> dict:
""" Returns a dict with aggregated month metrics - downtime and latency"""
downtime_results = Report.select(fn.SUM(Report.is_offline).alias(
'sum')).where((Report.my_id == my_id) & (Report.target_id == target_id)
& (Report.stamp >= start_date)
& (Report.stamp <= end_date))
latency_results = Report.select(fn.AVG(
Report.latency).alias('avg')).where((Report.my_id == my_id)
& (Report.target_id == target_id)
& (Report.stamp >= start_date)
& (Report.stamp <= end_date)
& (Report.latency >= 0))
if downtime_results[0].sum is None:
print(f'Sum result from db is None')
downtime = int(
downtime_results[0].sum) if downtime_results[0].sum is not None else 0
latency = latency_results[0].avg if latency_results[
0].avg is not None else 0
return {'downtime': downtime, 'latency': latency}
def get_stat_average():
query = Pokemon.select(fn.AVG(Pokemon.hp).alias('hp_avg'),
fn.AVG(Pokemon.special_attack).alias('special_attack_avg'),
fn.AVG(Pokemon.defense).alias('defense_avg'),
fn.AVG(Pokemon.attack).alias('attack_avg'),
fn.AVG(Pokemon.special_defense).alias('special_defense_avg'),
fn.AVG(Pokemon.speed).alias('speed_avg'),
)
# fn.AVG(Sample.value).over(partition_by=[Sample.counter]).alias('cavg'))
sample = query[0]
sample_dict = {'hp_avg': sample.hp_avg, 'special_attack_avg': sample.special_attack_avg,
'defense_avg': sample.defense_avg, 'attack_avg': sample.attack_avg,
'special_defense_avg': sample.special_defense_avg,
'speed_avg': sample.special_defense_avg}
return sample_dict
def get_best_users(chan=None,
since=datetime.timedelta(days=31),
limit=None):
""" Return the number of message by user """
query = [schema.Message.created_at > datetime.datetime.now() - since]
if chan:
query.append(schema.Message.chan == chan)
result = (schema.User.select(
schema.User.name,
fn.COUNT(schema.Message.id).alias("nb_messages"),
fn.AVG(fn.Length(schema.Message.message)).alias("avg_messages"),
fn.SUM(fn.Length(schema.Message.message)).alias("len_messages"),
).join(schema.Message, JOIN.LEFT_OUTER).where(*query).group_by(
schema.User.name).order_by(
fn.SUM(fn.Length(schema.Message.message)).desc()))
if limit:
result.limit(limit)
return result
def open_positions_report():
markets = [
lp.market
for lp in LongPosition.select(LongPosition.market).distinct()
]
results = []
result_str = "Open Positions:\n"
total_net = Decimal('0.0')
total_spent = Decimal('0.0')
for market in markets:
current_price = Candle.select().where(
Candle.market == market).order_by(
Candle.timestamp.desc()).limit(1)[0].close
(num_positions, quantity, spent, min, avg, max,
min_sell_price) = LongPosition.select(
fn.COUNT(LongPosition.id), fn.SUM(LongPosition.buy_quantity),
fn.SUM(LongPosition.buy_quantity * LongPosition.purchase_price),
fn.MIN(LongPosition.purchase_price),
fn.AVG(LongPosition.purchase_price),
fn.MAX(LongPosition.purchase_price),
fn.MIN(LongPosition.sell_price)).where(
LongPosition.market == market,
LongPosition.sell_timestamp.is_null(True)).scalar(
as_tuple=True)
if not num_positions:
continue
quantity = Decimal(quantity).quantize(Decimal('0.00000001'))
spent = Decimal(spent)
current_value = quantity * current_price
profit = (current_value - spent).quantize(Decimal('0.00000001'))
total_net += profit
total_spent += spent
current_profit_percentage = (current_value /
spent * Decimal('100.0')).quantize(
Decimal('0.01'))
results.append({
"market":
market,
"num_positions":
num_positions,
"min_position":
min.quantize(Decimal('0.00000001')),
"avg_position":
avg.quantize(Decimal('0.00000001')),
"max_position":
max.quantize(Decimal('0.00000001')),
"min_sell_price":
min_sell_price.quantize(Decimal('0.00000001')),
"min_profit_percentage":
(min_sell_price / min * Decimal('100.00')).quantize(
Decimal('0.01')),
"profit":
profit,
"current_profit_percentage":
current_profit_percentage,
"quantity":
quantity.normalize()
})
if total_spent > Decimal('0.0'):
total_percentage = (total_net / total_spent *
Decimal('100.0')).quantize(Decimal('0.01'))
else:
total_percentage = Decimal('0.0')
for result in sorted(results, key=lambda i: i['profit'], reverse=True):
result_str += f"{'{:>8}'.format(result['market'])}: {result['min_position']:0.8f} | {result['min_sell_price']:0.8f} ({'{:>6}'.format(str(result['min_profit_percentage']))}%) | {'{:>2}'.format(str(result['num_positions']))} | {'{:>6}'.format(str(result['current_profit_percentage']))}%\n"
result_str += f"{'-' * 53}\n"
result_str += f" total: {'{:>11}'.format(str(total_net))} | {'{:>6}'.format(str(total_percentage))}%\n"
return result_str
def getEventRange():
# asumming 5 min interval
clientID = request.args['clientID']
rangeFrom = int(request.args['from'])
if rangeFrom == -1:
rangeFrom = getMinEventTimestamp(clientID)
rangeTo = int(request.args['to'])
client = Client.get(Client.id == clientID)
dateFrom = fromTimestamp(rangeFrom)
dateTo = fromTimestamp(rangeTo)
delta = dateTo - dateFrom
if client.private and (client.owner != g.user):
return "", 403
if (delta.days <= 1):
# range <= 1 day, return all
# 288 points
print("1 day query")
events = (Event.select().where((Event.client_id == client)
& (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).order_by(
Event.timestamp))
elif (delta.days <= 3):
# range <= 3 days, 10 minutes period
# 432 points
print("3 days query")
events = (Event.select(
SQL("(timestamp - interval (MINUTE(timestamp) mod 10) MINUTE - interval SECOND(timestamp) SECOND) AS timestamp"
),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp), fn.HOUR(Event.timestamp),
SQL("MINUTE(timestamp) div 10")).order_by(Event.timestamp))
elif (delta.days <= 7):
# range <= 1 week, 20 minutes period
# 504 points
print("7 days query")
events = (Event.select(
SQL("(timestamp - interval (MINUTE(timestamp) mod 20) MINUTE - interval SECOND(timestamp) SECOND) AS timestamp"
),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp), fn.HOUR(Event.timestamp),
SQL("MINUTE(timestamp) div 20")).order_by(Event.timestamp))
elif (delta.days <= 30):
# range <= 1 month, 2 hour period, average
# 372 points per type
print("30 days query")
events = (Event.select(
SQL("(timestamp - interval MINUTE(timestamp) MINUTE - interval SECOND(timestamp) SECOND) AS timestamp"
),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp),
SQL("HOUR(timestamp) div 2")).order_by(Event.timestamp))
else:
print(">1 month query")
# range >=1 month, 1 day period, average
# min 365/366 datapoints per type
events = (Event.select(
fn.DATE(Event.timestamp).alias("timestamp"),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp)).order_by(Event.timestamp))
return json.jsonify(
[event.toFrontendObject(include_id=False) for event in events])
def query_nds_trial_stats(model_family, proposer, generator, model_spec, cell_spec, dataset,
num_epochs=None, reduction=None, include_intermediates=False):
"""
Query trial stats of NDS given conditions.
Parameters
----------
model_family : str or None
If str, can be one of the model families available in :class:`nni.nas.benchmark.nds.NdsTrialConfig`.
Otherwise a wildcard.
proposer : str or None
If str, can be one of the proposers available in :class:`nni.nas.benchmark.nds.NdsTrialConfig`. Otherwise a wildcard.
generator : str or None
If str, can be one of the generators available in :class:`nni.nas.benchmark.nds.NdsTrialConfig`. Otherwise a wildcard.
model_spec : dict or None
If specified, can be one of the model spec available in :class:`nni.nas.benchmark.nds.NdsTrialConfig`.
Otherwise a wildcard.
cell_spec : dict or None
If specified, can be one of the cell spec available in :class:`nni.nas.benchmark.nds.NdsTrialConfig`.
Otherwise a wildcard.
dataset : str or None
If str, can be one of the datasets available in :class:`nni.nas.benchmark.nds.NdsTrialConfig`. Otherwise a wildcard.
num_epochs : float or None
If int, matching results will be returned. Otherwise a wildcard.
reduction : str or None
If 'none' or None, all trial stats will be returned directly.
If 'mean', fields in trial stats will be averaged given the same trial config.
include_intermediates : boolean
If true, intermediate results will be returned.
Returns
-------
generator of dict
A generator of :class:`nni.nas.benchmark.nds.NdsTrialStats` objects,
where each of them has been converted into a dict.
"""
if proxy.obj is None:
proxy.initialize(load_benchmark('nds'))
fields = []
if reduction == 'none':
reduction = None
if reduction == 'mean':
for field_name in NdsTrialStats._meta.sorted_field_names:
if field_name not in ['id', 'config', 'seed']:
fields.append(fn.AVG(getattr(NdsTrialStats, field_name)).alias(field_name))
elif reduction is None:
fields.append(NdsTrialStats)
else:
raise ValueError('Unsupported reduction: \'%s\'' % reduction)
query = NdsTrialStats.select(*fields, NdsTrialConfig).join(NdsTrialConfig)
conditions = []
for field_name in ['model_family', 'proposer', 'generator', 'model_spec', 'cell_spec',
'dataset', 'num_epochs']:
if locals()[field_name] is not None:
conditions.append(getattr(NdsTrialConfig, field_name) == locals()[field_name])
if conditions:
query = query.where(functools.reduce(lambda a, b: a & b, conditions))
if reduction is not None:
query = query.group_by(NdsTrialStats.config)
for trial in query:
if include_intermediates:
data = model_to_dict(trial)
# exclude 'trial' from intermediates as it is already available in data
data['intermediates'] = [
{k: v for k, v in model_to_dict(t).items() if k != 'trial'} for t in trial.intermediates
]
yield data
else:
yield model_to_dict(trial)
def query_nlp_trial_stats(arch,
dataset,
reduction=None,
include_intermediates=False):
"""
Query trial stats of NLP benchmark given conditions, including config(arch + dataset) and training results after 50 epoch.
Parameters
----------
arch : dict or None
If a dict, it is in the format that is described in
:class:`nni.nas.benchmark.nlp.NlpTrialConfig`. Only trial stats matched will be returned.
If none, all architectures in the database will be matched.
dataset : str or None
If specified, can be one of the dataset available in :class:`nni.nas.benchmark.nlp.NlpTrialConfig`.
Otherwise a wildcard.
reduction : str or None
If 'none' or None, all trial stats will be returned directly.
If 'mean', fields in trial stats will be averaged given the same trial config.
Please note that some trial configs have multiple runs which make "reduction" meaningful, while some may not.
include_intermediates : boolean
If true, intermediate results will be returned.
Returns
-------
generator of dict
A generator of :class:`nni.nas.benchmark.nlp.NlpTrialStats` objects,
where each of them has been converted into a dict.
"""
fields = []
if reduction == 'none':
reduction = None
if reduction == 'mean':
for field_name in NlpTrialStats._meta.sorted_field_names:
if field_name not in ['id', 'config']:
fields.append(
fn.AVG(getattr(NlpTrialStats,
field_name)).alias(field_name))
elif reduction is None:
fields.append(NlpTrialStats)
else:
raise ValueError('Unsupported reduction: \'%s\'' % reduction)
query = NlpTrialStats.select(*fields, NlpTrialConfig).join(NlpTrialConfig)
conditions = []
if arch is not None:
conditions.append(NlpTrialConfig.arch == arch)
if dataset is not None:
conditions.append(NlpTrialConfig.dataset == dataset)
for trial in query.where(functools.reduce(lambda a, b: a & b, conditions)):
if include_intermediates:
data = model_to_dict(trial)
# exclude 'trial' from intermediates as it is already available in data
data['intermediates'] = [{
k: v
for k, v in model_to_dict(t).items() if k != 'trial'
} for t in trial.intermediates]
yield data
else:
yield model_to_dict(trial)
def get_hp_moy():
avg = Pokemon.select(fn.AVG(Pokemon.hp).alias('avg_hp'))
return avg
def index():
ge_dim = ["A", "B", "C", "D", "E", "F"]
ge_top3 = {}
for dimension in ge_dim:
ge_top3[dimension] = get_grade_data().where(
CourseData.dimension == dimension,
GradeData.course_no.contains("3T") == False,
GradeData.course_no.contains("3N") == False).order_by(
GradeData.GPA.desc()).limit(3)
dep_mapping = {
"AC": "工程技術研究所自動化及控制學程(自動化及控制研究所)",
"FN": "財務金融研究所",
"AD": "建築系",
"GD": "全球發展工程學士學位學程",
"AT": "應用科技學士學位學程",
"GE": "人文社會學科",
"BA": "企業管理系",
"GX": "學士後綠能產業機電工程學士學位學程",
"BB": "醫學工程學士學位學程",
"HC": "不分系學士班",
"BE": "醫學工程研究所",
"IB": "(學士後)智慧財產權學士學位學程",
"CC": "文學(人文社會學科)外文(語言中心)體育(體育室)[106學年前]",
"IM": "工業管理系",
"CD": "創意設計學士班",
"MA": "MBA",
"CE": "工程學士班",
"MB": "管理學士班",
"CH": "化學工程系",
"ME": "機械工程系",
"CI": "色彩與照明科技研究所",
"MG": "管理研究所",
"CS": "資訊工程系",
"MI": "資訊管理系",
"CT": "營建工程系",
"MS": "工程技術研究所材料科技學程(材料科技研究所)",
"CX": "色彩影像與照明科技學士學位學程",
"PA": "專利研究所",
"DE": "設計研究所",
"PE": "體育(體育室)",
"DT": "工商設計系",
"RD": "高階科技研發碩士學位學程",
"EC": "電資學士班",
"SA": "(學務處服務型通識課程、軍訓課程[104學年前])",
"EE": "電機工程系",
"SG": "新加坡管理碩士在職專班",
"EN": "應用科技研究所(工程技術研究所[100學年度前])",
"TB": "科技管理學士學位學程",
"EO": "光電工程研究所",
"TC": "通識教育中心、軍訓課程[104學年起]",
"EP": "師資培育中心",
"TE": "先進科技全英語外國學生專班",
"ET": "電子工程系",
"TM": "科技管理研究所",
"FB": "財務金融學士學位學程",
"TX": "材料科學與工程系(高分子工程系[99學年前])",
"FE": "語言中心",
"VE": "數位學習與教育研究所(技術及職業教育研究所[99學年前])",
"FL": "應用外語系"
}
dep_gpa = []
for key in dep_mapping:
query = GradeData.select(fn.AVG(GradeData.GPA).alias('avg'))\
.where(GradeData.course_no.contains(key))
if query.exists():
dep_gpa.append({
"no": key,
"detail": dep_mapping[key],
"gpa": query.get().avg
})
dep_gpa.sort(key=lambda data: data['gpa'], reverse=True)
return render_template("index.html",
ge_top3=ge_top3,
ge_dim=ge_dim,
dep_gpa=dep_gpa)
