Geek asks and answers

Choose a fraction of data with a lot of data using MySQL

I have hundreds of thousands of price points spanning 40 years plus. I would like to build a query that will only return 3000 common data points, with the last 500 being the last data points, and the other 2500 is just a sample of the rest of the data, evenly distributed.

Is it possible to do this in one request? How to select only a sample of a large amount of data? This is a small example of what I mean to get the whole sample from two other 2500 data points:

1
2
3    
4
5
6
7
8
9
10

And I want to return something like this:

1
5
10

Here's the query for the last 500:

SELECT * FROM price ORDER BY time_for DESC LIMIT 500

I'm not sure how to fetch data from other data points.

+5
source share
2 answers

Try the following:

(SELECT * FROM price ORDER BY time_for DESC LIMIT 500)
UNION ALL
(SELECT * FROM price WHERE time_for < (SELECT time_for FROM price ORDER BY time_for LIMIT 500, 1) ORDER BY rand() LIMIT 2500)
ORDER BY time_for

. , . ?

, , . , ORDER BY rand() LIMIT . , . .

+5

, , , , . , MOD . MYSQL , , , 100%. .

  (  SELECT p1.*
       FROM price p1
   ORDER BY p1.time_for DESC
      LIMIT 500  )

   UNION ALL

  (  SELECT @i := @i + 1 AS row_num,
            p2.*
       FROM price p2,
            (SELECT @i: = 0)
      WHERE row_num > 500
        AND (row_num % 500) = 0
   ORDER BY time_for DESC  )

500 . 500- , . , . , , 2500 .

+3

More articles:


All Articles