SHARE
TWEET

Duplicate Transit Queries

Dyrcona Sep 7th, 2018 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select date_trunc('minute', source_send_time), source, dest,
  2. target_copy, count(id)
  3. from action.transit_copy
  4. where source_send_time > '2018-05-28'
  5. group by 1,2,3,4
  6. having count(id) > 1;
  7.  
  8. select date_trunc('second', source_send_time), source, dest,
  9. target_copy, count(id)
  10. from action.transit_copy
  11. where source_send_time > '2018-05-28'
  12. group by 1,2,3,4
  13. having count(id) > 1;
  14.  
  15. --I ran the next two to get some kind of idea what happened before the upgrade to 3.0 and the web staff client.
  16. select date_trunc('second', source_send_time), source, dest,
  17. target_copy, count(id)
  18. from action.transit_copy
  19. where source_send_time between '2018-01-01' and '2018-05-29'
  20. group by 1,2,3,4
  21. having count(id) > 1
  22. order by 1;
  23. select date_trunc('minute', source_send_time), source, dest,
  24. target_copy, count(id)
  25. from action.transit_copy
  26. where source_send_time between '2018-01-01' and '2018-05-29'
  27. group by 1,2,3,4
  28. having count(id) > 1
  29. order by 1;
  30.  
  31. -- Hour granularity turns up even more.
  32. select date_trunc('hour', source_send_time), source, dest,
  33. target_copy, count(id)
  34. from action.transit_copy
  35. where source_send_time > '2018-05-28'
  36. group by 1,2,3,4
  37. having count(id) > 1
  38. order by 1;
  39.  
  40. -- Experimenting with 1 day granularity and adding more fields.
  41. select date_trunc('day', source_send_time), source, dest,
  42. target_copy, count(id)
  43. from action.transit_copy
  44. where source_send_time > '2018-05-28'
  45. group by 1,2,3,4
  46. having count(id) > 1
  47. order by 1;
  48.  
  49. select date_trunc('day', source_send_time), source, dest,
  50. target_copy, prev_hop, count(id)
  51. from action.transit_copy
  52. where source_send_time > '2018-05-28'
  53. group by 1,2,3,4,5
  54. having count(id) > 1
  55. order by 1;
  56.  
  57. select date_trunc('day', source_send_time), source, dest,
  58. target_copy, prev_hop, prev_dest, copy_status, count(id)
  59. from action.transit_copy
  60. where source_send_time > '2018-05-28'
  61. group by 1,2,3,4,5,6,7
  62. having count(id) > 1
  63. order by 1;
  64.  
  65. select date_trunc('day', source_send_time), source, dest,
  66. target_copy, prev_hop, prev_dest, count(id)
  67. from action.transit_copy
  68. where source_send_time > '2018-05-28'
  69. group by 1,2,3,4,5,6
  70. having count(id) > 1
  71. order by 1;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top