API tools faq
paste
Advertisement
Guest User

sql example

a guest
Dec 9th, 2015
156
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.49 KB | None | 0 0
raw download clone embed print report
  1. create table data (oid INT, type INT);
  2. INSERT INTO data VALUES
  3. (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
  4. (1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),(1,0),
  5. (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),
  6. (2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),(2,2),
  7. (3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),(3,2),
  8. (4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),(4,0),
  9. (6,0),
  10. (8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),(8,2),
  11. (9,0),
  12. (11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),(11,3),
  13. (12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),(12,0),
  14. (13,4),
  15. (13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),(13,5),
  16. (13,1),(13,1),
  17. (13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),(13,2),
  18. (14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),(14,4),
  19. (14,1),(14,1),(14,1),(14,1),
  20. (14,2),(14,2),(14,2),
  21. (15,3),(15,3),(15,3),(15,3),(15,3),(15,3),(15,3),(15,3),(15,3),(15,3),
  22. (16,0),(16,0),(16,0),(16,0),(16,0),(16,0),(16,0),(16,0),(16,0),(16,0),(16,0),(16,0),(16,0),
  23. (18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),(18,4),
  24. (18,1),(18,1),(18,1),(18,1),(18,1),
  25. (19,1),(19,1),(19,1),(19,1),(19,1),
  26. (19,2),(19,2),(19,2),(19,2),(19,2),
  27. (19,3),(19,3),(19,3);
  28.  
  29.  
  30. WITH v AS (
  31. SELECT oid,
  32. type,
  33. COUNT(1) AS total_per_oid_per_type
  34. FROM data
  35. GROUP BY oid, type
  36. ),
  37. s AS (
  38. SELECT oid,
  39. MAX(total_per_oid_per_type) AS max_total_per_oid
  40. FROM v
  41. GROUP BY oid
  42. ),
  43. totals AS (
  44. SELECT oid,
  45. SUM(total_per_oid_per_type) AS total_per_oid
  46. FROM v
  47. GROUP BY oid
  48. )
  49. SELECT v.oid,
  50. v.type,
  51. v.total_per_oid_per_type,
  52. (v.total_per_oid_per_type + 0.0) / totals.total_per_oid AS percentage
  53. FROM v
  54. INNER JOIN s ON v.oid = s.oid AND v.total_per_oid_per_type = s.max_total_per_oid
  55. INNER JOIN totals ON v.oid = totals.oid
  56. ORDER BY v.oid, v.type;
  57.  
  58. output
  59. oid type total_per_oid_per_type percentage
  60. 0 0 22 1.0
  61. 1 0 22 1.0
  62. 2 2 104 0.63030303030303
  63. 3 2 63 1.0
  64. 4 0 34 1.0
  65. 6 0 1 1.0
  66. 8 2 76 1.0
  67. 9 0 1 1.0
  68. 11 3 33 1.0
  69. 12 0 55 1.0
  70. 13 2 255 0.891608391608392
  71. 14 4 148 0.954838709677419
  72. 15 3 10 1.0
  73. 16 0 13 1.0
  74. 18 4 137 0.964788732394366
  75. 19 1 5 0.384615384615385
  76. 19 2 5 0.384615384615385
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!