Hello. There is a request to select data for statistics on goods. It is necessary to group the number of orders, views, the amount of orders and other data. With the extraction of no questions.

The main question is grouping by day (even by week and month)

SELECT SUM(orders_payed.seller_summ) AS seller_summ, SUM(orders_payed.partner_summ) AS partner_summ, SUM(orders_payed.cnt) AS orders_payed_count, SUM(visits.cnt) AS count_visits FROM products LEFT JOIN (SELECT product_parent_id, SUM(IF(seller_id=2, seller_summ, 0)) AS seller_summ, SUM(IF(partner_id=2, partner_summ, 0)) AS partner_summ, COUNT(1) AS cnt FROM orders WHERE status=1 AND DATE(payed_at) = "2017-02-16" GROUP BY product_parent_id ) AS orders_payed ON orders_payed.product_parent_id = products.id LEFT JOIN (SELECT product_parent_id, COUNT(1) AS cnt FROM product_visits WHERE DATE(created_at) = "2017-02-16" GROUP BY product_parent_id ) AS visits ON visits.product_parent_id = products.id WHERE products.active = 1 AND products.active_partners_system = 1 AND products.parent_id IS NULL AND products.id IN (...)

Roughly speaking, now I have to make 7 such requests changing dates, I need to do one request for 7 days (weeks, months). Those. display statistics for all selected products line by line (by day).

I understand that you need to group in join-s and somehow combine it all.

Thanks to all!

PS: Request reduced to save space)

The issue was resolved by creating an additional cache table.

An additional table that accumulates data in the necessary section from other tables (using triggers) is the best option for storing large amounts of information in order to speed up the work of queries.

  • there is the operator BETWEEN - mymedia
  • one
    It is extremely difficult to advise something without seeing the source tables. You would have placed some samples on the sql field. - Ella Svetlaya
  • You then torture statistics for the year to collect from several tables. The table is better one and perhaps. habrahabr.ru/post/66151 The question remains as to whether such detailed statistics are needed for such a period. With the introduction of a table with totals by day, you can delete completely old periods from visits. it is necessary to think very seriously, and why and in what section may we need information about visits, say a year ago - Mike

1 answer 1

 SELECT P.DT, SUM(count_visits) count_visits, SUM(seller_summ) seller_summ, SUM(partner_summ) partner_summ, SUM(orders_payed.cnt) AS orders_payed_count FROM ( SELECT products.id, DATE("2017-02-16")+interval SX day as DT, COUNT(visits.product_parent_id) AS count_visits FROM products JOIN seqnum S ON SX<=7 LEFT JOIN product_visits visits ON visits.product_parent_id=products.id and created_at>= DATE("2017-02-16")+interval SX day and created_at < DATE("2017-02-16")+interval S.X+1 day WHERE products.active = 1 AND products.active_partners_system = 1 AND products.parent_id IS NULL AND products.id IN (...) group by DATE("2017-02-16")+interval SX day, products.id ) P LEFT JOIN (SELECT product_parent_id, date(payed_at) DT SUM(IF(seller_id=2, seller_summ, 0)) AS seller_summ, SUM(IF(partner_id=2, partner_summ, 0)) AS partner_summ, COUNT(1) AS cnt FROM orders WHERE status=1 AND payed_at >= "2017-02-16" and payed_at < DATE("2017-02-16")+interval 8 day GROUP BY product_parent_id, date(payed_at) ) AS orders_payed ON orders_payed.DT=P.DT and P.id=orders_payed.product_parent_id GROUP BY P.DT

The date generation is used as in this answer to create the seqnum table as described there.

  • @Solvik for other periods two problems: 1. date intervals, for the week apparently created_at>= DATE("2017-02-16")+interval SX*7 day and created_at < DATE("2017-02-16")+interval SX*7+1 day for weeks or +interval SX month AND +interval SX month+interval 1 day for months. Only the starting date is better to do at the beginning of the week / month. And 2. grouping up to a week / month and not up to a day. it's more interesting here, I think to use date_format mysql.ru/docs/man/Date_and_time_functions.html with the appropriate format that would include year and week or month - Mike
  • @Solvik If you leave the grouping up to the day, then you need to stupidly extend the sampling interval, but all the conditions should be kept one day wide. those. stupidly increase the condition SX<=7 to the desired number of days. - Mike
  • @Solvik Or I’ve started to trick already ... we will group in both parts until the day, which means that the width of the sampling period does not matter. so you can and so. try it is necessary :) - Mike
  • @Solvik I think it is necessary to solve those 1 and 2 tasks separately. we change the interval as we like, in the P and orders_payed subqueries we leave the grouping up to 1 day, so that the data will be stuck together correctly (they are just id and day glued. And then we just change the last GROUP BY line to collect it up to the required period, there already any date_format. Well, the number of weeks or something else is easily regulated by the selected numbers from seqnum - Mike
  • @Solvik on SO did not accept such a detailed correspondence. I suggest to delete comments. I still delete my question. then here as they will not be needed - Mike