def get_query(self):
"""Returns SQL query that will be used to create the M-formatted table
"""
start_time = self.__start_time
stop_time = self.__stop_time
if self.__convert_to_unix_time:
start_time = utils.to_unix_time(start_time)
stop_time = utils.to_unix_time(stop_time)
try:
# If times are numbers, their string representations don't have
# surrounding single quotes. Otherwise, they do
float(start_time)
except ValueError:
start_time = "'{}'".format(start_time)
try:
float(stop_time)
except ValueError:
stop_time = "'{}'".format(start_time)
col_specs = self.__col_specs
conn = self.__conn
table_name = self.__rg_table_name
# get all features
sql_features = "SELECT DISTINCT {} FROM {};".format(col_specs["feature"], table_name)
feat_names = [row[0] for row in conn.execute(sql_features)]
# figure out aggregations
aggregations = {
feat_name: self.__aggregations[feat_name]
if self.__aggregations.has_key(feat_name)
else self.__default_aggregation
for feat_name in feat_names
}
# Now we build the complicated sql query
sql_select_clause = "SELECT id_tbl.id, {} ".format(
", ".join(["{0}_tbl.val AS {0}".format(feat) for feat in feat_names])
)
sql_from_clause_top = ("FROM ({}SELECT DISTINCT {} AS id FROM {}) id_tbl " "LEFT JOIN ").format(
"(" * len(feat_names), col_specs["unit_id"], table_name
)
sql_from_clause_features = "LEFT JOIN ".join(
[
(
"(SELECT {unit_id_col} AS id, {aggr}({val_col}) AS val FROM "
"{table_name} WHERE "
"{feature_col} = '{feat_name}' AND "
"(({start_time_col} >= {start_time} "
" AND {start_time_col} <= {stop_time}) "
" OR {start_time_col} IS NULL) AND "
"(({stop_time_col} >= {start_time} "
" AND {stop_time_col} <= {stop_time}) "
" OR {stop_time_col} IS NULL) "
"GROUP BY id) {feat_name}_tbl ON "
"id_tbl.id = {feat_name}_tbl.id) "
).format(
unit_id_col=col_specs["unit_id"],
aggr=aggregations[feat_name],
val_col=col_specs["val"],
table_name=table_name,
feature_col=col_specs["feature"],
start_time_col=col_specs["start_time"],
start_time=start_time,
stop_time_col=col_specs["stop_time"],
stop_time=stop_time,
feat_name=feat_name,
)
for feat_name in feat_names
]
)
# TODO we can probably do something more sophisticated than just
# throwing the user's directives in here
sql_where_clause = ""
if self.__selections:
sql_where_clause = "WHERE " + "AND ".join(["({})".format(sel) for sel in self.__selections])
sql_select = "{} {} {} {}".format(
sql_select_clause, sql_from_clause_top, sql_from_clause_features, sql_where_clause
)
return sql_select
