0

I can't use NTILE() because I'm currently on MYSQL 5.7 so I was wondering how would I go about converting this to be usable in 5.7 without NTILE. Here's the query I'd like to convert:

SELECT
      clientid,
      ntile(4) over (
        order by
          last_order_date
      ) AS `rfm_recency`,
      ntile(4) over (
        order by
          count_order
      ) AS `rfm_frequency`,
      ntile(4) over (
        order by
          avg_amount
      ) AS `rfm_monetary`
    FROM
      (
        SELECT
          `clientid`,
          MAX(`date`) AS `last_order_date`,
          COUNT(`id`) AS `count_order`,
          AVG(`price`) AS `avg_amount`
        FROM
          `design`
        GROUP BY
          `clientid`
      ) AS t
  ) AS p```
Rick James
  • 135,179
  • 13
  • 127
  • 222
dydx
  • 157
  • 15
  • You can find the basic concept to simulate window functions in [this thread](https://stackoverflow.com/a/8913662). While relatively straight forward, it doesn't look like fun to convert your query, so you should probably try it yourself and then add a version (including incorrect result) where you are stuck at. To maybe attract someone that does it for you, you should definitely add your `create table` and sample data (as `insert`s) (and as an sql fiddle [1](http://sqlfiddle.com/) [2](https://dbfiddle.uk/?rdbms=mysql_5.7)) to reduce the work someone has to do before he can start helping you. – Solarflare Apr 15 '20 at 08:35
  • 1
    See the tag I added; "groupwise-max" uses some of the same techniques. – Rick James Apr 21 '20 at 22:25

0 Answers0