Posted By

lrivers on 11/28/10


Tagged

postgres web2py


Versions (?)

web2py SQL for performance


 / Published in: SQL
 

This is the SQL code that generates the report side (the web2py version also updates the rows in the minute interval with the id of a summary table that gets a row inserted with the summary from that period)

  1. SELECT
  2. stringid AS string,
  3. macaddr AS panel,
  4. date_trunc('minute',CAST(reqtime AS timestamp)) AS minute,
  5. --volts in
  6. round(cast(avg(vi) AS decimal),3) AS avg_vi,
  7. round(cast(min(vi) AS decimal),3) AS min_vi,
  8. round(cast(max(vi) AS decimal),3) AS max_vi,
  9. --volts out
  10. round(cast(avg(vo) AS decimal),3) AS avg_vo,
  11. round(cast(min(vo) AS decimal),3) AS min_vo,
  12. round(cast(max(vo) AS decimal),3) AS max_vo,
  13. --current in
  14. round(cast(avg(ii) AS decimal),3) AS avg_ii,
  15. round(cast(min(ii) AS decimal),3) AS min_ii,
  16. round(cast(max(ii) AS decimal),3) AS max_ii,
  17. --current out
  18. round(cast(avg(io) AS decimal),3) AS avg_io,
  19. round(cast(min(io) AS decimal),3) AS min_io,
  20. round(cast(max(io) AS decimal),3) AS max_io,
  21. --power in
  22. round(cast(avg(pi) AS decimal),3) AS avg_pi,
  23. round(cast(min(pi) AS decimal),3) AS min_pi,
  24. round(cast(max(pi) AS decimal),3) AS max_pi,
  25. --power out
  26. round(cast(avg(po) AS decimal),3) AS avg_po,
  27. round(cast(min(po) AS decimal),3) AS min_po,
  28. round(cast(max(po) AS decimal),3) AS max_po,
  29. --volts loss
  30. sum(case when vi > vo then 1 else 0 end) AS samples_with_volts_loss,
  31. round(cast(sum(case when vi > vo then vi-vo else 0 end) AS decimal) ,3) AS volts_loss,
  32. --volts gain
  33. sum(case when vi < vo then 1 else 0 end) AS samples_with_volts_gain,
  34. round(cast(sum(case when vi < vo then vo-vi else 0 end) AS decimal) ,3) AS volts_gain,
  35. --current loss
  36. sum(case when ii > io then 1 else 0 end) AS samples_with_current_loss,
  37. round(cast(sum(case when ii > io then ii-io else 0 end) AS decimal) ,3) AS current_loss,
  38. --power gain
  39. sum(case when ii < io then 1 else 0 end) AS samples_with_current_gain,
  40. round(cast(sum(case when ii < io then io-ii else 0 end) AS decimal) ,3) AS current_gain,
  41. --power loss
  42. sum(case when pi > po then 1 else 0 end) AS samples_with_power_loss,
  43. round(cast(sum(case when pi > po then pi-po else 0 end) AS decimal) ,3) AS power_loss,
  44. --power gain
  45. sum(case when pi < po then 1 else 0 end) AS samples_with_power_gain,
  46. round(cast(sum(case when pi < po then po-pi else 0 end) AS decimal) ,3) AS power_gain,
  47. --total samples
  48. count(*) AS total_samples
  49. FROM data_table
  50. WHERE reqtime < timeofday()
  51. GROUP BY vstring, stringid, macaddr, date_trunc('minute',CAST(reqtime AS timestamp))
  52. ORDER BY 4,1,2,3

Report this snippet  

You need to login to post a comment.