Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you would add an AUTO_INCREMENT PRIMARY KEY onto your table, that would be aolveable.</p> <p>The table schema as you have presented it is invalid (PRIMARY KEY on <code>timestamp</code>, but no such column).</p> <p>Would you mind if we:</p> <pre><code>ALTER TABLE mytable DROP PRIMARY KEY, ADD COLUMN id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY </code></pre> <p>?</p> <p>If so, then the following could be done, I'll present in steps:</p> <pre><code>SELECT TIMESTAMPDIFF(SECOND, m1.ts, m2.ts) AS diff_seconds, m2.cnt - m1.cnt AS diff_cnt FROM mytable m1 JOIN mytable m2 ON (m1.id = m2.id-1) ; </code></pre> <p>The above shows the difference in time and in cnt between consecutive samples. Let's add a third column:</p> <pre><code>SELECT TIMESTAMPDIFF(SECOND, m1.ts, m2.ts) AS diff_seconds, m2.cnt - m1.cnt AS diff_cnt, (m2.cnt - m1.cnt)/TIMESTAMPDIFF(SECOND, m1.ts, m2.ts) AS cnt_per_second FROM mytable m1 JOIN mytable m2 ON (m1.id = m2.id-1) ; </code></pre> <p>I evaluated cnt_per_second. Multiply by 60 to get cnt per minute, and so on.</p> <p>Now, the total average would be:</p> <pre><code>SELECT avg(cnt_per_second) FROM ( SELECT TIMESTAMPDIFF(SECOND, m1.ts, m2.ts) AS diff_seconds, m2.cnt - m1.cnt AS diff_cnt, (m2.cnt - m1.cnt)/TIMESTAMPDIFF(SECOND, m1.ts, m2.ts) AS cnt_per_second FROM mytable m1 JOIN mytable m2 ON (m1.id = m2.id-1) ) sel_diff ; </code></pre> <p>Add original n1.ts to first query if you want to know <em>when</em> a diff was recorded, and so you will also be able to know the average count events in a given time period.</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL: extract counter difference from time series data
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USShlomi Noach
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMySQL: extract counter difference from time series data
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSorry, the primary key was on the `ts` column, shorthand for timestamp. My fault while writing the query here and there and editing it on the fly. I've edited the question to correct it, and I'll now take my time to read and understand your whole answer :)
      singulars
      1. PO
      1. USLuke404
    2. COThen allow me to assist in clarifying the solution. The idea is to compare row #1 with row #2, compare row #2 with row #3 etc. Each two rows compared produce a difference in time. They also make for difference in count. delta(count)/delta(time) is the average count per sec for each sample.
      singulars
      1. PO
      1. USShlomi Noach
    3. COYeah I understood that and I also tested and applied your solution. Thank you very much.
      singulars
      1. PO
      1. USLuke404
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload