There are several landing pages that display several ad banners. A surfer (user) comes to a landing page and clicks on banners.
There are 2 statistics tables in MySQL database (aggregated by some fields). Statistics on arrivals on landings and clicks on banners. Entry table ( report_in ) - displays statistics of surfers' arrivals to landing pages, exit table ( report_out ) - click statistics on these banners.
Simplified DDL.

Simplified DDL.

It turns out the surfer comes in, records +1 display on the current day in the table report_in according to the corresponding landing page ID. When a user clicks on a banner, the report_out records the link to report_in, the day and +1 click on the clicked banner ID.
Thus, we see how many users came to the landing pages and how many clicks were made on their banners.

The following situations may occur:

  • the user came to the landing page and clicked nothing (i.e. there is an entry in report_in , but not in report_out )
  • the user came to the landing page and clicked on several banners (i.e. there is an entry in report_in and several entries from report_out refer to it ).

How do you get such a table as a result of a join?
enter image description here

It turns out that Landing # 5 contains # 10, # 11 banners. 100 users came to this landing page and clicked 20 and 30 times on banners 10 and 11, respectively. But on landing page 6, 50 users came and never clicked on banners.

You cannot report report_out to report_in , because the records of report_in are duplicated, and this table is needed for further aggregation (for example, group by landing pages and month and calculate how many came and went by banners).

This is something like FULL JOIN. So far the idea has only come through UNION:

SELECT date AS DATE, landing_id AS LANDING_ID, count_impressions AS LANDING_IMPRESSIONS, NULL AS BANNER_ID, NULL AS BANNER_CLICKS FROM report_in UNION SELECT NULL AS DATE, NULL AS LANDING_ID, NULL AS LANDING_IMPRESSIONS, banner_id AS BANNER_ID, count_clicks AS BANNER_CLICKS FROM report_out

But somehow a crutch. maybe there is some more correct way?

    2 answers 2

    Make the usual left join. Grouping it does not hurt. It is necessary to group first by date-ID_lending and at the same time take max (number of parishes), while at the same time calmly count the sum (number of clicks), count (banner id) then wrap it still with one select and pre-group it to the required

     select landing_id,sum(LANDING_IMPRESSIONS) as LANDING_IMPRESSIONS, sum(BANNER_CNT) as BANNER_CNT,sum(BANNER_CLICKS) as BANNER_CLICKS from ( select I.date AS DATE, I.landing_id AS LANDING_ID, max(I.count_impressions) AS LANDING_IMPRESSIONS, count(O.banner_id) AS BANNER_CNT,sum(O.count_clicks) AS BANNER_CLICKS from report_in I left join report_out O on O.id_report_in=I.id group by I.date,I.landing_id ) A group by landing_id

      SELECT * FROM Report_in inner join Report_out on Report_in.id = Report_out.ID_REPORT_IN; Instead of * you can list the fields you need.

      • so I won’t get records from report_in that have no connection to report_out . Those. Landings that were shown, but no one clicked on from banners. - Ray