違い - Mysqlは複数のLEFT JOINを含む他の列に基づいて異なる




sql 複数テーブル 結合 (2)

私は時間と活動の制約と支払いタイプを考慮していないが、それはそれらの行に沿った何かではない概念の証明です。

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

アイデアは、まず最初に、異なる契約を1つの結果にマージし、「TYPE」という列に型を格納する(これがUNIONクエリの目的です)、一度各契約が一度正確であるような素晴らしいテーブルを作成すると、あなたの望む結果をかなり簡単に得ることができます。 私はちょうどあなたが契約の各タイプの合計と数を得る方法の概要を述べました。 もちろん、最終的なクエリはもう少し複雑になりますが、コアの考え方は同じでなければなりません。

しかし、(一時的な)ビューを使用したくないという発言にもかかわらず、私はあなたに試してみることをお勧めします。それはあなたの懸念です。クエリをあまりにも醜くすることはありません。主に1人の訪問者の統計だけを見たい場合や、不要な行がマテリアライズされないため、時間やアクティビティなどでさらにフィルタをかけたい場合などです。 。 しかし、それは大きなデータセットでクエリを試していないので印象です。

私はLEFT JOINを一緒にしたいテーブルを5つ持っています。 テーブルには、訪問者、オファー、契約1、契約2、契約3があります。

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'

ここで問題となるのは、contract1、contract2、およびcontract3には共通の列がないため、一緒に結合することができないということです。 したがって、コントラクト1の場合は20行、コントラクト2の場合は30、契約3の場合は50行の代わりに、すべてのコンビネーションをすべて取得します。 彼らは訪問者に基づいて参加し、テーブルを提供しているからです。 クエリの最後に単純なGROUP BYを指定すると問題は解決しますが、ENDでGROUP BYを使用すると、1つではなく複数の行が作成されます。 また、IDで訪問者をカウントし、IDで提供する部分については、他のすべての結果を消去します... SELECTのcount()部分でDISTINCTを使用できますが、sum()ではなく、契約のPRICE IDがない場合でも同じである可能性があります(例2のように、チョコレートはIDが異なる2行、それぞれ10ドルで同じPRICEです)。

だから私の質問です:

DISTINCT IDを持つ契約1、契約2、契約3の価格だけをSUMで集計する方法はありますか? VIEWを作成せずに可能ですか?

私はまた、LEFT JOINの中でGROUP BYを試しましたが、もう一度LEFTジョイントすると3つのコントラクトテーブルが一緒になりました。

期待される結果の例:

私が上に述べたその時の地平線では、35の提案と5の契約を持つ80人の訪問者が1000ユーロの合計、12の契約2と686ユーロの合計、12のユーロの合計で3つの契約3を期待する。 8列のデータを持つ1行です。

期待された結果の代わりに、私は80人の訪問者、35の提供、180の契約1(合計も悪い)、180契約2(合計も悪い)、180契約3(合計も悪い)を得た。


CTEでは( 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;

CTEがなければ、一時テーブルを使用するようにCTEを書き換えることができます。

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;




distinct