有时候复杂的逻辑处理可以交给SQL来处理,自认为是比java处理要快点。

举个例子:如何统计每个品牌的的偏好用户数?

SQL之 UNION ALL 和UNION-LMLPHP

当时我做的处理是在java中处理这些品牌,若是品牌相同,就加一,但是这种方法很慢,用户体验很不好。

SQL之 UNION ALL 和UNION-LMLPHP

后来才发现sql处理速度会很快,就用UNION ALL 来处理。在这里简单说一下UNION和UNION ALL区别,这两个都是用于合并select的结果集。只不过UNION ALL可以允许有重复值。一条sql就可以解决问题。如下:

SELECT
T.BRANDNAME AS brandName,
SUM(T.NUMB) AS userNum
FROM
(
SELECT
PREFER_BRAND1 AS BRANDNAME,
COUNT(PREFER_BRAND1) AS NUMB
FROM
TAS_TAS.TERM_CONT_RECOM_ITEM_201605
GROUP BY
PREFER_BRAND1
UNION ALL
SELECT
PREFER_BRAND2 AS BRANDNAME,
COUNT(PREFER_BRAND2) AS NUMB
FROM
TAS_TAS.TERM_CONT_RECOM_ITEM_201605
GROUP BY
PREFER_BRAND2
UNION ALL
SELECT
PREFER_BRAND3 AS BRANDNAME,
COUNT(PREFER_BRAND3) AS NUMB
FROM
TAS_TAS.TERM_CONT_RECOM_ITEM_201605
GROUP BY
PREFER_BRAND3 ) T
WHERE
T.BRANDNAME!='-9'
GROUP BY
T.BRANDNAME
ORDER BY
SUM(T.NUMB) DESC

这样就可以知道每个品牌的偏好人数了。

05-02 10:12