基于包含多个左连接的其他列的Mysql和。

Ive got 5 tables that I'd like to LEFT JOIN together. Tables are : visitors, offers, contracts1, contracts2 and contracts3.

我有5张桌子要放在一起。表是:访客、报价、合同、合同和合同。

QUERY:

查询:

SELECT 
        count(DISTINCT visitors.ID) as visitors, 
        sum(
        CASE
        WHEN offers.ACTIVE = 1 THEN 1
        ELSE 0
        END) as offers, 
        count(contracts1.ID) as contracts1, sum(contracts1.PRICE) as sum_contracts1, 
        count(contracts2.ID) contracts2, 
        sum(
        CASE
        WHEN contracts2.PAYMENT = 'YEARLY' THEN contracts2.PRICE
        WHEN contracts2.PAYMENT = 'TWICE' THEN contracts2.PRICE*2
        ELSE contracts2.PRICE*4
        END) as sum_contracts2,
        count(contracts3.ID) as contracts3, sum(contracts3.PRICE) as sum_contracts3
        FROM visitors 
        LEFT JOIN offersON offers.VISITOR_ID = visitors.ID AND (offers.IP > 100 OR offers.IP < 0)
        LEFT JOIN contracts1 ON 
        (offers.ID = contracts1.ID_OFFER)
        LEFT JOIN contracts2 ON 
        (offers.ID = contracts2.ID_OFFER)
        LEFT JOIN contracts3 ON 
        (offers.ID = contracts3.ID_OFFER)
        WHERE  visitors.TIME >= '2017-01-01 00:00:00' AND visitors.TIME <= '2017-05-25 23:59:59'

Problem here is, that contracts1, contracts2 and contracts3 have no common column in order to be joined together. So instead of 20 rows for contracts1, 30 for contracs2 and 50 for contracts3 i get all the combination for all of them. Because they are joined based on visitors and offers tables. Simple GROUP BY in the end of the query would normally solve the problem, but if I use GROUP BY in the END for one of those tables (or all of them), it will create MULTIPLE ROWS instead of 1 that I want. And also it would erase all the other result for the part where i count visitors by ID and also offers by ID ... I can use DISTINCT on count() parts of the SELECT but not one the sum() because PRICE of the contracts may be same even though IDs are not (you know like for example 2 chocolates are 2 rows with different IDs but same PRICE for 10 dollars each).

这里的问题是，契约1、契约2和契约3没有共同的列来连接在一起。所以不是20行1,30行2,50行3我得到了所有的组合。因为它们是基于访问者和提供的表格而加入的。查询末尾的Simple GROUP BY通常可以解决这个问题，但是如果我最终对其中一个表(或所有表)使用GROUP BY，它将创建多个行，而不是我想要的1。而且它也会删除所有其他的结果在我按ID计数的部分，以及按ID提供的部分……我可以使用count()的不同部分，但不能使用sum()，因为即使id不是，合同的价格也可能是相同的(例如，2巧克力有两行不同的id，但是每个10美元的价格是相同的)。

So my question is:

我的问题是:

Is there any way to SUM only those PRICES of contracts1, contracts2 and contracts3, that have DISTINCT ID and though get rid of adding up the duplicates? And is it possible without creating VIEW?

有什么办法只对那些有明显标识、但又不把重复标识加起来的合同、合同和合同的价格进行求和呢?有没有可能不创建视图?

I also tried GROUP BY inside of the LEFT JOIN but again when i LEFT JOINED all 3 contracts tables together, even though i GROUPED them before I ended up with duplicates.

我也尝试了在左连接内部进行分组，但是当我离开时，我将所有的3个契约表合并在一起，即使我在得到副本之前对它们进行了分组。

Example of expected result:

预期结果的例子:

In that time horizon which I stated above I would expect: 80 visitors that have 35 offers and 5 contracts1 with sum of 1000 euros, 12 contracts2 with sum of 686 euros and 3 contracts3 with sum of 12 euros. It is ONE ROW with 8 columns of data.

在我上面所述的时间范围内，我预计会有80位客人，他们有35个报价，5个合同，共计1000欧元，12个合同，共计686欧元，3个合同，共计12欧元。它是一行，包含8列数据。

Instead of expected result I got: 80 visitors, 35 offers, 180 contracts1 (sum is also bad), 180 contracts2 (sum is also bad), 180 contracts3 (sum is also bad).

与预期的结果相反，我得到了:80个访客，35个提供，180个合同(总数也很糟糕)，180个合同(总数也很糟糕)，180个合同(总数也很糟糕)。

2 个解决方案

#1

With CTEs (Supported by MariaDB 10.2.1) I would write something like this:

有了CTEs(由MariaDB 10.2.1支持)，我会这样写:

WITH v AS (
    SELECT ID as VISITOR_ID
    FROM visitors 
    WHERE visitors.TIME >= '2017-01-01 00:00:00'
      AND visitors.TIME <= '2017-05-25 23:59:59'
), o AS (
    SELECT offers.ID as ID_OFFER
    FROM v
    JOIN offers USING(VISITOR_ID)
    WHERE offers.ACTIVE = 1
      AND (offers.IP > 100 OR offers.IP < 0)
), c1 AS (
    SELECT count(*) as contracts1, sum(contracts1.PRICE) as sum_contracts1
    FROM o JOIN contracts1 USING(ID_OFFER)
), c2 AS (
    SELECT
        count(*) contracts2, 
        sum(CASE contracts2.PAYMENT
            WHEN 'YEARLY' THEN contracts2.PRICE
            WHEN 'TWICE'  THEN contracts2.PRICE*2
            ELSE contracts2.PRICE*4
        END) as sum_contracts2
    FROM o JOIN contracts2 USING(ID_OFFER)
), c3 AS (
    SELECT count(*) as contracts3, sum(contracts3.PRICE) as sum_contracts3
    FROM o JOIN contracts3 USING(ID_OFFER)
)
    SELECT c1.*, c2.*, c3.*,
        (SELECT count(*) FROM v) as visitors,
        (SELECT count(*) FROM o) as offers,
    FROM c1, c2, c3;

Without CTEs you can rewrite it to use temporary tables:

没有CTEs，您可以重写它以使用临时表:

CREATE TEMPORARY TABLE v AS
    SELECT ID as VISITOR_ID
    FROM visitors 
    WHERE visitors.TIME >= '2017-01-01 00:00:00'
      AND visitors.TIME <= '2017-05-25 23:59:59';

CREATE TEMPORARY TABLE o AS
    SELECT offers.ID as ID_OFFER
    FROM v
    JOIN offers USING(VISITOR_ID)
    WHERE offers.ACTIVE = 1
      AND (offers.IP > 100 OR offers.IP < 0);

CREATE TEMPORARY TABLE c1 AS
    SELECT count(*) as contracts1, sum(contracts1.PRICE) as sum_contracts1
    FROM o JOIN contracts1 USING(ID_OFFER);

CREATE TEMPORARY TABLE c2 AS
    SELECT
        count(*) contracts2, 
        sum(CASE contracts2.PAYMENT
            WHEN 'YEARLY' THEN contracts2.PRICE
            WHEN 'TWICE'  THEN contracts2.PRICE*2
            ELSE contracts2.PRICE*4
        END) as sum_contracts2
    FROM o JOIN contracts2 USING(ID_OFFER);

CREATE TEMPORARY TABLE c3 AS
    SELECT count(*) as contracts3, sum(contracts3.PRICE) as sum_contracts3
    FROM o JOIN contracts3 USING(ID_OFFER);

SELECT c1.*, c2.*, c3.*,
    (SELECT count(*) FROM v) as visitors,
    (SELECT count(*) FROM o) as offers,
FROM c1, c2, c3;

#2

Just a proof of concept where I don't account for the time and activity constraints as well as the payment type, but couldn't it be something along those lines?

这只是一个概念的证明，我不考虑时间和活动约束以及支付类型，但它不是沿着这些线吗?

SELECT
   VISITOR_ID,
   SUM(CASE WHEN TYPE="contract1" THEN 1 else 0 END) as c1_count,
   SUM(CASE WHEN TYPE="contract1" THEN PRICE else 0 END) as c1_total_price,
   SUM(CASE WHEN TYPE="contract2" THEN 1 else 0 END) as c2_count,
   SUM(CASE WHEN TYPE="contract2" THEN PRICE else 0 END) as c2_total_price,
   SUM(CASE WHEN TYPE="contract3" THEN 1 else 0 END) as c3_count,
   SUM(CASE WHEN TYPE="contract3" THEN PRICE else 0 END) as c3_total_price 
FROM (
    (SELECT "contract1" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts1) 
    UNION
    (SELECT "contract2" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts2)
    UNION
    (SELECT "contract3" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts3)
 ) as all_contracts 
 JOIN offers on offers.id = all_contracts.ID_OFFER
 JOIN visitors on visitors.ID = offers.VISITOR_ID
 GROUP BY visitors.ID

The idea is that first you merge the different contracts into one result where you store their type in a column called "TYPE" (that's the purpose of the UNION queries) and once you have such a nice table where each contract is exactly once, you can get your desired result quite straightforward. I just outlined how you get the sum and count for each type of contract. Of course, the final query would be a bit more complicated but the core idea should be the same.

第一想法是,你将不同的合同合并到一个结果其类型存储在一个称为“类型”列(联合查询的目的),一旦你有这样一个表,其中每个合同就是一次,你可以得到你想要的结果非常简单。我刚刚概述了如何得到每种类型的合同的总和和计数。当然，最终的查询可能会有点复杂，但核心思想应该是相同的。

But despite your statement that you don't want to use (temporary) views, I would encourage you to try it - I have a feeling that putting those "all_contracts" joined with offers and visitors into a temporary view would improve the performance, if that's your concern, without making the query too ugly, mainly in the case when you would want to see the stats just for one visitor or to filter them further (by time, activity and so on), because unnecessary rows won't be materialized. But that's just an impression since I haven't tried the query on a bigger data set - you can play with it.

尽管你不想使用的声明(临时)的观点,我会鼓励你尝试它,我有一种感觉,把这些“all_contracts”与提供和游客到一个临时视图将改善性能,如果这是你的关心,没有查询太丑了,主要是在当你想要看到的数据只是一个访客或进一步过滤(按时间、活动等等),因为不必要的行不会兑现。但这只是一个印象，因为我还没有在更大的数据集上尝试过这个查询——您可以使用它。

#1