<p>I have a MySQL table with approximately 3000 rows per user. One of the columns is a datetime field, which is mutable, so the rows aren't in chronological order.</p>

<p>I'd like to visualize the time distribution in a chart, so I need a number of individual datapoints. 20 datapoints would be enough.</p>

<p>I could do this:</p>

<pre><code>select timefield from entries where uid = ? order by timefield;
</code></pre>

<p>and look at every 150th row.</p>

<p>Or I could do 20 separate queries and use <code>limit 1</code> and <code>offset</code>. </p>

<p>But there must be a more efficient solution...</p>

[{'answer_id': 3691, 'author': 'Michal Sznajder', 'author_id': 501, 'author_profile': 'https://Stackoverflow.com/users/501', 'pm_score': 1, 'selected': False, 'text': "<p>Something like this came to my mind</p>\n\n<pre><code>select @rownum:=@rownum+1 rownum, entries.* \nfrom (select @rownum:=0) r, entries \nwhere uid = ? and rownum % 150 = 0\n</code></pre>\n\n<p>I don't have MySQL at my hand but maybe this will help ...</p>\n"}, {'answer_id': 3775, 'author': 'Ryan Ahearn', 'author_id': 75, 'author_profile': 'https://Stackoverflow.com/users/75', 'pm_score': 0, 'selected': False, 'text': '<p><a href="https://stackoverflow.com/questions/3682/sql-distribution-of-table-in-time#3691">@Michal</a></p>\n\n<p>For whatever reason, your example only works when the where @recnum uses a less than operator.  I think when the where filters out a row, the rownum doesn\'t get incremented, and it can\'t match anything else.</p>\n\n<p>If the original table has an auto incremented id column, and rows were inserted in chronological order, then this should work:</p>\n\n<pre><code>select timefield from entries\nwhere uid = ? and id % 150 = 0 order by timefield;\n</code></pre>\n\n<p>Of course that doesn\'t work if there is no correlation between the id and the timefield, unless you don\'t actually care about getting evenly spaced timefields, just 20 random ones.</p>\n'}, {'answer_id': 30562, 'author': 'Scott Noyes', 'author_id': 3254, 'author_profile': 'https://Stackoverflow.com/users/3254', 'pm_score': 0, 'selected': False, 'text': '<p>Do you really care about the individual data points? Or will using the statistical aggregate functions on the day number instead suffice to tell you what you wish to know?</p>\n\n<ul>\n<li><a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_avg" rel="nofollow noreferrer">AVG</a></li>\n<li><a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_stddev-pop" rel="nofollow noreferrer" title="STDDEV_POP">STDDEV_POP</a></li>\n<li><a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_variance" rel="nofollow noreferrer" title="VARIANCE">VARIANCE</a></li>\n<li><a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_to-days" rel="nofollow noreferrer" title="TO_DAYS">TO_DAYS</a></li>\n</ul>\n'}, {'answer_id': 30600, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 0, 'selected': False, 'text': "<pre><code>select timefield\nfrom entries\nwhere rand() = .01 --will return 1% of rows adjust as needed.\n</code></pre>\n\n<p>Not a mysql expert so I'm not sure how rand() operates in this environment.</p>\n"}, {'answer_id': 155901, 'author': 'Bill Karwin', 'author_id': 20860, 'author_profile': 'https://Stackoverflow.com/users/20860', 'pm_score': 4, 'selected': True, 'text': "<p>Michal Sznajder almost had it, but you can't use column aliases in a WHERE clause in SQL.  So you have to wrap it as a derived table.  I tried this and it returns 20 rows:</p>\n\n<pre><code>SELECT * FROM (\n    SELECT @rownum:=@rownum+1 AS rownum, e.*\n    FROM (SELECT @rownum := 0) r, entries e) AS e2\nWHERE uid = ? AND rownum % 150 = 0;\n</code></pre>\n"}, {'answer_id': 155951, 'author': 'Cade Roux', 'author_id': 18255, 'author_profile': 'https://Stackoverflow.com/users/18255', 'pm_score': 1, 'selected': False, 'text': '<p>As far as visualization, I know this is not the periodic sampling you are talking about, but I would look at all the rows for a user and choose an interval bucket, SUM within the buckets and show on a bar graph or similar.  This would show a real "distribution", since many occurrences within a time frame may be significant.</p>\n\n<pre><code>SELECT DATEADD(day, DATEDIFF(day, 0, timefield), 0) AS bucket -- choose an appropriate granularity (days used here)\n     ,COUNT(*)\nFROM entries\nWHERE uid = ?\nGROUP BY DATEADD(day, DATEDIFF(day, 0, timefield), 0)\nORDER BY DATEADD(day, DATEDIFF(day, 0, timefield), 0)\n</code></pre>\n\n<p>Or if you don\'t like the way you have to repeat yourself - or if you are playing with different buckets and want to analyze across many users in 3-D (measure in Z against x, y uid, bucket):</p>\n\n<pre><code>SELECT uid\n    ,bucket\n    ,COUNT(*) AS measure\nFROM (\n    SELECT uid\n        ,DATEADD(day, DATEDIFF(day, 0, timefield), 0) AS bucket\n    FROM entries\n) AS buckets\nGROUP BY uid\n    ,bucket\nORDER BY uid\n    ,bucket\n</code></pre>\n\n<p>If I wanted to plot in 3-D, I would probably determine a way to order users according to some meaningful overall metric for the user.</p>\n'}, {'answer_id': 22067305, 'author': 'Michiel de Mare', 'author_id': 136, 'author_profile': 'https://Stackoverflow.com/users/136', 'pm_score': 0, 'selected': False, 'text': '<p>For my reference - and for those using postgres - Postgres 9.4 will have ordered set aggregates that should solve this problem:</p>\n\n<pre><code>SELECT percentile_disc(0.95) \nWITHIN GROUP (ORDER BY response_time) \nFROM pageviews;\n</code></pre>\n\n<p>Source: <a href="http://www.craigkerstiens.com/2014/02/02/Examining-PostgreSQL-9.4/" rel="nofollow noreferrer">http://www.craigkerstiens.com/2014/02/02/Examining-PostgreSQL-9.4/</a></p>\n'}]
['https://Stackoverflow.com/questions/3682', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/136/']