Advertisement
Guest User

Untitled

a guest
Jun 22nd, 2018
71
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.04 KB | None | 0 0
  1. DROP TABLE IF EXISTS scratch.relcomments;
  2. CREATE TABLE scratch.relcomments AS
  3. SELECT A.post_id, A.page_id, B.created_on - A.created_at as delay
  4. FROM facebook.comment_contents B, scratch.relposts A
  5. WHERE A.post_id = B.post_id;
  6.  
  7. DROP TABLE IF EXISTS scratch.delaytimings;
  8. CREATE TABLE scratch.delaytimings AS
  9. SELECT page_id, date_trunc('hour',delay) as hours, count(*) as count
  10. FROM scratch.relcomments
  11. GROUP BY page_id, hours
  12. ORDER BY page_id, hours;
  13.  
  14. DROP TABLE IF EXISTS scratch.delaytimingsoverlaps;
  15. CREATE TABLE scratch.delaytimingsoverlaps AS
  16. SELECT A.count as countA, B.count as countB, A.page_id as page_idA, B.page_id as page_idB
  17. FROM scratch.delaytimings A INNER JOIN scratch.delaytimings B
  18. ON A.hours = B.hours
  19. WHERE A.page_id < B.page_id;
  20.  
  21. DROP TABLE IF EXISTS scratch.delaytimingsoverlaps_counts;
  22. CREATE TABLE scratch.delaytimingsoverlaps_counts AS
  23. SELECT sum(countA) as tcountA, sum(countB) as tcountB, page_idA, page_idB
  24. FROM scratch.delaytimingsoverlaps
  25. GROUP BY page_idA, page_idB;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement