google-bigquery - 如何在连接查询中选择两次相同的列？

Question

我如何计算性别是 M 还是 F，有点像

SELECT count(N.gender) 
FROM `DATABASE_T`   as T
LEFT JOIN `DATABASE_N` as N
ON 
T.ENCUESTA = N.ENCUESTA AND
T.P_DEPTO = N.P_DEPTO AND
T.P_MUNIC = N.P_MUNIC AND
T.COD_VEREDA = N.COD_VEREDA AND
T.PAIS = N.PAIS and
T.UC_UO = N.UC_UO

WHERE  N.ID_PROD=1 and N.gender="M"

标签： google-bigquery

Answer 1

SELECT countif(N.gender = 'M') as M, countif(N.gender = 'F') as F
FROM `DATABASE_T`   as T
LEFT JOIN `DATABASE_N` as N
ON 
T.ENCUESTA = N.ENCUESTA AND
T.P_DEPTO = N.P_DEPTO AND
T.P_MUNIC = N.P_MUNIC AND
T.COD_VEREDA = N.COD_VEREDA AND
T.PAIS = N.PAIS and
T.UC_UO = N.UC_UO

WHERE  N.ID_PROD=1