优化查询 - 从同一个子查询中选择 COUNT 和 SUM [英] Optimize Query - Select COUNT and SUM from the same subquery

问题描述

我试图在 Stackoverflow 上找到解决方案，也许我的措辞有误.

I tried to find the solution to this on Stackoverflow, maybe my wording is wrong.

我有一个查询需要很长时间才能执行.我相信有一些简单的方法可以改进它.例如，我两次使用相同的子查询来显示两个不同的列(sum 和 count)，但在尝试自己解决时遇到了几个错误.

I have a query which takes to long to execute. I am sure there are simple ways to improve it. For example, I use the same sub-query twice for displaying two different columns (sum and count) but encountered several errors while trying to solve it on my own.

SELECT u.ID,
u.user_email AS mail, 
u.user_login AS userName, 
u.user_registered  AS signUpDate, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'first_name' limit 1) as firstName, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'last_name' limit 1) as lastName, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'billing_phone' limit 1) as billingPhone, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'shipping_phone' limit 1) as shippingPhone, 
(SELECT COUNT(meta_value) from wp_postmeta WHERE meta_key = '_order_total' and post_id IN (select post_id from wp_postmeta where meta_value = u.ID and meta_key = '_customer_user')) as orderCount,
(SELECT SUM(meta_value) from wp_postmeta WHERE meta_key = '_order_total' and post_id IN (select post_id from wp_postmeta where meta_value = u.ID and meta_key = '_customer_user')) as moneySpent 
FROM wp_users u;

推荐答案

@Timur，根据您在第一个答案的评论中提出的问题；你必须做这样的事情.您仍然需要连接，但 wp_usermeta 表不需要那么多连接.您仍然需要加入 wp_postmeta 两次，因为您无法在 '_customer_user' meta_key 上进行加入并同时检索 '_order_total' meta_key 在单个连接中.

@Timur, as per your question in the comments of the first answer; you'd have to do something like this. You still need joins but you don't need as many for the wp_usermeta table. You will still need to join to wp_postmeta twice, because you can't do a join on the '_customer_user' meta_key and simultaneously retrieve the '_order_total' meta_key in a single join.

只是关于 MAX(CASE WHEN um.meta_key = '...' THEN um.meta_value END) 逻辑的注释；它相当于 MAX(CASE WHEN um.meta_key = '...' THEN um.meta_value ELSE NULL END).

Just a note on the MAX(CASE WHEN um.meta_key = '...' THEN um.meta_value END) logic; it's the equivalent to MAX(CASE WHEN um.meta_key = '...' THEN um.meta_value ELSE NULL END).

SELECT    u.ID, 
          u.user_email AS mail,
          u.user_login AS userName,
          u.user_registered AS signUpDate,
          MAX(CASE WHEN um.meta_key = 'first_name'     THEN um.meta_value END) AS firstName, 
          MAX(CASE WHEN um.meta_key = 'last_name'      THEN um.meta_value END) AS lastName, 
          MAX(CASE WHEN um.meta_key = 'billing_phone'  THEN um.meta_value END) AS billingPhone, 
          MAX(CASE WHEN um.meta_key = 'shipping_phone' THEN um.meta_value END) AS shippingPhone,
          COUNT(pval.meta_value) AS orderCount,
          SUM(pval.meta_value) AS moneySpent
FROM      wp_users AS u
LEFT JOIN wp_usermeta AS um   ON u.ID = um.user_id
LEFT JOIN wp_postmeta AS pm   ON u.ID = pm.meta_value      AND pm.meta_key = '_customer_user'
LEFT JOIN wp_postmeta AS pval ON pm.post_id = pval.post_id AND pval.meta_key = '_order_total'
WHERE     (um.meta_key IN ('first_name', 'last_name', 'billing_phone', 'shipping_phone') 
           OR um.meta_key IS NULL)
GROUP BY  u.ID, 
          u.user_email,
          u.user_login,
          u.user_registered;

我不是 100% 确定逻辑.让我知道是否需要调整

I'm not 100% sure on the logic. Let me know if it needs tweaking

这篇关于优化查询 - 从同一个子查询中选择 COUNT 和 SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！