MongoDB-MySQL SUM(CASE WHEN)等效? [英] MongoDB - MySQL SUM (CASE WHEN) Equivalent?

问题描述

我正在尝试对Mongo进行一些测试，我发现了一些更简单的MySQL查询等效于Mongo.

I'm trying to do some testing with Mongo, I have figured some of the simpler MySQL queries Mongo equivalent.

我的查询比较复杂，需要帮助...

I have this query that is a little more complex that I need help with...

SELECT DISTINCT dims_user,
    COUNT(DISTINCT asset_name) AS asset_count,
    COUNT(DISTINCT system_name) AS station_count,
    SUM(CASE WHEN details ='viewed' then 1 Else 0 end) AS viewed_count,
    SUM(CASE WHEN details Like 'Viewed Web%' then 1 Else 0 end) AS Web_count,
    SUM(CASE WHEN details = 'ThumbView' then 1 Else 0 end) AS ThumbView_count,
    SUM(CASE WHEN details Like 'Exported%' then 1 Else 0 end) AS Exported_count,
    SUM(CASE WHEN details Like '%Print%' then 1 Else 0 end) AS Printed_count
FROM asset_log GROUP BY dims_user;

推荐答案

在这里我同意Rudu，因为您应该尝试将详细信息分解为mongo文档的键.

I would agree with Rudu here as you should try to break the details into keys of a mongo document.

文档中可能包含这样的对象:

The document might contain an object like this:

details:
{
  viewed: true
  thumb_view: true
  web_viewed: false
  exported: true
  ...
}

如果您不重组数据，则查询将需要无法使用MongoDB索引功能的无根正则表达式.

If you don't restructure the data, the query will require non-rooted regular expressions which are unable to use the indexing features of MongoDB.

但是，无论您是否决定这样做，都将为此使用map reduce.您可以在映射过程中发出包含详细信息的项(通过使用正则表达式对其进行处理，或者只是以重组形式发出键)，然后在化简阶段对其求和.

However, regardless of if you decide to do that or not you will want to use map reduce for this. You could emit items that include the details during the map (either by processing them with regular expressions or simply emiting the keys in restructured form) and sum them in the reduce phase.

您可以在文档

这篇关于MongoDB-MySQL SUM(CASE WHEN)等效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！