def _build_sum_of_similarity_scores(
self,
top_n,
normalization_factor,
top_n_as="_top_n",
normalization_factor_as="_normalization_factor"):
cf_scores = SelectQuery()
cf_scores.select_from(top_n, alias=top_n_as)
join_condition = Column(f"{self.based_column}_2",
top_n_as).eq_null_unsafe(
Column(self.based_column,
normalization_factor_as))
cf_scores.join(normalization_factor,
JoinTypes.INNER,
join_condition,
alias=normalization_factor_as)
cf_scores.group_by(
Column(f"{self.based_column}_1", table_name=top_n_as))
cf_scores.group_by(
Column(self.pivot_column, table_name=normalization_factor_as))
cf_scores.select(Column(f"{self.based_column}_1", table_name=top_n_as),
alias=self.based_column)
cf_scores.select(
Column(self.pivot_column, table_name=normalization_factor_as))
cf_scores.select(self._get_user_item_similarity_formula(
top_n_as, normalization_factor_as),
alias=constants.SCORE_COLUMN_NAME)
cf_scores.order_by(Column(self.based_column))
cf_scores.order_by(Column(constants.SCORE_COLUMN_NAME),
direction="DESC")
return cf_scores
toSQL(sb, Dialects.MYSQL)
# -------------------------------------------------------------------------------- NOTEBOOK-CELL: CODE
# CTE
sb = SelectQuery()
sb.select(Constant(33))
sb.with_cte(sql.SelectQuery().select_from('cte_table').alias('myCTE'))
sb.select_from('myTable')
toSQL(sb, Dialects.GREENPLUM)
# -------------------------------------------------------------------------------- NOTEBOOK-CELL: CODE
# Order
sb = SelectQuery()
sb.select_from('myTable')
sb.order_by(Column('orderCol'))
toSQL(sb, Dialects.MYSQL)
# -------------------------------------------------------------------------------- NOTEBOOK-CELL: CODE
# Subquery
q1 = SelectQuery()
q1.select_from('table')
q1.alias('innerQuery')
q2 = SelectQuery()
q2.select_from(q1)
toSQL(q2, Dialects.MYSQL)
# -------------------------------------------------------------------------------- NOTEBOOK-CELL: CODE