API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 20th, 2019
101
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.74 KB | None | 0 0
raw download clone embed print report
  1. ----------------------------------------------
  2. line_no |holiday_flag| passenger_up|plan_date
  3. ----------------------------------------------
  4. 1 0 22 2019-02-02
  5. 1 0 15 2019-02-02
  6. 1 0 14 2019-02-02
  7. 1 1 0 2019-02-03
  8. 1 1 11 2019-02-03
  9. 1 1 3 2019-02-03
  10. 1 1 15 2019-02-03
  11. 1 3 4 2019-02-17
  12. 1 3 1 2019-02-17
  13. 1 3 3 2019-02-17
  14. 1 3 4 2019-02-17
  15. 1 3 2 2019-02-17
  16. 1 3 0 2019-02-17
  17. 1 3 1 2019-02-17
  18. 1 2 0 2019-02-02
  19. 1 2 0 2019-02-02
  20. 1 2 0 2019-02-02
  21. 1 2 3 2019-02-02
  22.  
  23. -----------------------------------
  24. line_no |holiday_flag| passenger_up
  25. -----------------------------------
  26. 1 0 4858
  27. 1 1 2176
  28. 1 3 4638
  29. -----------------------------------
  30.  
  31. SELECT
  32. line_no,
  33. holiday_flag,
  34. round(passenger_up / day_count) as passenger_up
  35. FROM
  36. (
  37. SELECT
  38. line_no,
  39. holiday_flag,
  40. SUM (passenger_up) AS passenger_up,
  41. SUM (day_count) AS day_count
  42. FROM
  43. (
  44. SELECT
  45. line_no,
  46. CASE
  47. WHEN holiday_flag = 0
  48. OR holiday_flag = 2 THEN
  49. 0
  50. ELSE
  51. holiday_flag
  52. END AS holiday_flag,
  53. SUM (passenger_up) AS passenger_up,
  54. COUNT (DISTINCT plan_date) AS day_count
  55. FROM
  56. ext_passenger_flow_tag
  57. WHERE
  58. line_no = 1
  59. AND plan_date >= '2019-01-31'
  60. AND plan_date <= '2019-02-20'
  61. GROUP BY
  62. line_no,
  63. holiday_flag
  64. ) AS B
  65. GROUP BY
  66. B.line_no,
  67. B.holiday_flag
  68. ) AS C
  69.  
  70. Subquery Scan on c (cost=4456.55..4456.72 rows=4 width=40) (actual time=19.316..19.322 rows=3 loops=1)
  71. Output: c.line_no, c.holiday_flag, round((c.passenger_up / c.day_count), 0)
  72. Buffers: shared hit=372
  73. -> GroupAggregate (cost=4456.55..4456.66 rows=4 width=72) (actual time=19.312..19.316 rows=3 loops=1)
  74. Output: b.line_no, b.holiday_flag, sum(b.passenger_up), sum(b.day_count)
  75. Group Key: b.line_no, b.holiday_flag
  76. Buffers: shared hit=372
  77. -> Sort (cost=4456.55..4456.56 rows=4 width=24) (actual time=19.305..19.305 rows=4 loops=1)
  78. Output: b.line_no, b.holiday_flag, b.passenger_up, b.day_count
  79. Sort Key: b.line_no, b.holiday_flag
  80. Sort Method: quicksort Memory: 25kB
  81. Buffers: shared hit=372
  82. -> Subquery Scan on b (cost=4359.02..4456.51 rows=4 width=24) (actual time=17.501..19.279 rows=4 loops=1)
  83. Output: b.line_no, b.holiday_flag, b.passenger_up, b.day_count
  84. Buffers: shared hit=372
  85. -> GroupAggregate (cost=4359.02..4456.47 rows=4 width=26) (actual time=17.500..19.276 rows=4 loops=1)
  86. Output: ext_passenger_flow_tag.line_no, CASE WHEN ((ext_passenger_flow_tag.holiday_flag = 0) OR (ext_passenger_flow_tag.holiday_flag = 2)) THEN 0 ELSE (ext_passenger_flow_tag.holiday_flag)::integer END, sum(ext_passenger_flow_tag.passenger_up), count(DISTINCT ext_passenger_flow_tag.plan_date), ext_passenger_flow_tag.holiday_flag
  87. Group Key: ext_passenger_flow_tag.line_no, ext_passenger_flow_tag.holiday_flag
  88. Buffers: shared hit=372
  89. -> Sort (cost=4359.02..4378.50 rows=7790 width=14) (actual time=15.231..16.075 rows=7899 loops=1)
  90. Output: ext_passenger_flow_tag.line_no, ext_passenger_flow_tag.holiday_flag, ext_passenger_flow_tag.passenger_up, ext_passenger_flow_tag.plan_date
  91. Sort Key: ext_passenger_flow_tag.holiday_flag
  92. Sort Method: quicksort Memory: 563kB
  93. Buffers: shared hit=372
  94. -> Bitmap Heap Scan on public.ext_passenger_flow_tag (cost=2174.75..3855.50 rows=7790 width=14) (actual time=9.908..12.697 rows=7899 loops=1)
  95. Output: ext_passenger_flow_tag.line_no, ext_passenger_flow_tag.holiday_flag, ext_passenger_flow_tag.passenger_up, ext_passenger_flow_tag.plan_date
  96. Recheck Cond: ((ext_passenger_flow_tag.line_no = 1) AND (ext_passenger_flow_tag.line_subno = 1) AND (ext_passenger_flow_tag.updown = 1) AND (ext_passenger_flow_tag.plan_date >= '2019-01-31'::date) AND (ext_passenger_flow_tag.plan_date <= '2019-02-20'::date) AND (ext_passenger_flow_tag.interval_flag = 2))
  97. Heap Blocks: exact=86
  98. Buffers: shared hit=372
  99. -> Bitmap Index Scan on ext_pf_tag_index1 (cost=0.00..2172.80 rows=7790 width=0) (actual time=9.876..9.876 rows=7899 loops=1)
  100. Index Cond: ((ext_passenger_flow_tag.line_no = 1) AND (ext_passenger_flow_tag.line_subno = 1) AND (ext_passenger_flow_tag.updown = 1) AND (ext_passenger_flow_tag.plan_date >= '2019-01-31'::date) AND (ext_passenger_flow_tag.plan_date <= '2019-02-20'::date) AND (ext_passenger_flow_tag.interval_flag = 2))
  101. Buffers: shared hit=286
  102. Planning time: 0.533 ms
  103. Execution time: 19.450 ms
  104.  
  105. SELECT line_no,
  106. CASE WHEN holiday_flag = 2
  107. THEN 0
  108. ELSE holiday_flag
  109. END AS holiday_flag,
  110. round(CAST(sum(passenger_up)
  111. AS double precision) /
  112. CAST(count(DISTINCT plan_date)
  113. AS double precision)) AS passenger_up
  114. FROM ext_passenger_flow_tag
  115. WHERE line_no = 1
  116. AND plan_date BETWEEN DATE '2019-01-31' AND DATE '2019-02-20'
  117. GROUP BY line_no, holiday_flag;
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!