API tools faq
paste
Advertisement
Guest User

miband2_raw.sql

a guest
Oct 29th, 2016
330
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 28.29 KB | None | 0 0
raw download clone embed print report
  1. CREATE TEMP TABLE _PersonParams(Height,Weight);
  2.  
  3. .READ db/health.SQL
  4.  
  5. CREATE TEMP TABLE _CalList(Dist,Cal);
  6. INSERT INTO _CalList (Dist,Cal) VALUES (40.233,0.95);
  7. INSERT INTO _CalList (Dist,Cal) VALUES (53.645,1.19);
  8. INSERT INTO _CalList (Dist,Cal) VALUES (67.056,1.41);
  9. INSERT INTO _CalList (Dist,Cal) VALUES (80.467,1.57);
  10. INSERT INTO _CalList (Dist,Cal) VALUES (93.878,1.78);
  11. INSERT INTO _CalList (Dist,Cal) VALUES (107.29,2.36);
  12. INSERT INTO _CalList (Dist,Cal) VALUES (120.7,2.97);
  13. INSERT INTO _CalList (Dist,Cal) VALUES (134.11,3.79);
  14. INSERT INTO _CalList (Dist,Cal) VALUES (160.94,4.67);
  15. INSERT INTO _CalList (Dist,Cal) VALUES (187.76,5.24);
  16. INSERT INTO _CalList (Dist,Cal) VALUES (214.58,5.62);
  17. INSERT INTO _CalList (Dist,Cal) VALUES (241.4,6.1);
  18. INSERT INTO _CalList (Dist,Cal) VALUES (268.23,6.91);
  19. INSERT INTO _CalList (Dist,Cal) VALUES (295.05,7.62);
  20. INSERT INTO _CalList (Dist,Cal) VALUES (321.87,9.05);
  21. INSERT INTO _CalList (Dist,Cal) VALUES (348.69,9.43);
  22. INSERT INTO _CalList (Dist,Cal) VALUES (375.52,10.95);
  23.  
  24. CREATE TEMP TABLE _Hght(h1);
  25. INSERT INTO _Hght SELECT Height*0.42/100 FROM _PersonParams;
  26.  
  27. ALTER TABLE date_data ADD COLUMN "data_bytes" TEXT;
  28.  
  29. UPDATE date_data
  30.    SET data_bytes = SUBSTR(DATA, 34, 5761);
  31.  
  32. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'A', '......') ;
  33. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'B', '.....#') ;
  34. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'C', '....#.') ;
  35. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'D', '....##') ;
  36. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'E', '...#..') ;
  37. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'F', '...#.#') ;
  38. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'G', '...##.') ;
  39. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'H', '...###') ;
  40. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'I', '..#...') ;
  41. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'J', '..#..#') ;
  42. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'K', '..#.#.') ;
  43. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'L', '..#.##') ;
  44. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'M', '..##..') ;
  45. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'N', '..##.#') ;
  46. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'O', '..###.') ;
  47. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'P', '..####') ;
  48. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'Q', '.#....') ;
  49. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'R', '.#...#') ;
  50. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'S', '.#..#.') ;
  51. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'T', '.#..##') ;
  52. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'U', '.#.#..') ;
  53. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'V', '.#.#.#') ;
  54. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'W', '.#.##.') ;
  55. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'X', '.#.###') ;
  56. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'Y', '.##...') ;
  57. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'Z', '.##..#') ;
  58. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'a', '.##.#.') ;
  59. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'b', '.##.##') ;
  60. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'c', '.###..') ;
  61. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'd', '.###.#') ;
  62. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'e', '.####.') ;
  63. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'f', '.#####') ;
  64. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'g', '#.....') ;
  65. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'h', '#....#') ;
  66. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'i', '#...#.') ;
  67. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'j', '#...##') ;
  68. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'k', '#..#..') ;
  69. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'l', '#..#.#') ;
  70. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'm', '#..##.') ;
  71. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'n', '#..###') ;
  72. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'o', '#.#...') ;
  73. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'p', '#.#..#') ;
  74. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'q', '#.#.#.') ;
  75. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'r', '#.#.##') ;
  76. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 's', '#.##..') ;
  77. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 't', '#.##.#') ;
  78. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'u', '#.###.') ;
  79. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'v', '#.####') ;
  80. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'w', '##....') ;
  81. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'x', '##...#') ;
  82. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'y', '##..#.') ;
  83. UPDATE date_data SET data_bytes = REPLACE(data_bytes, 'z', '##..##') ;
  84. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '0', '##.#..') ;
  85. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '1', '##.#.#') ;
  86. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '2', '##.##.') ;
  87. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '3', '##.###') ;
  88. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '4', '###...') ;
  89. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '5', '###..#') ;
  90. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '6', '###.#.') ;
  91. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '7', '###.##') ;
  92. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '8', '####..') ;
  93. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '9', '####.#') ;
  94. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '+', '#####.') ;
  95. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '/', '######') ;
  96.  
  97. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '.', '0'      ) ;
  98. UPDATE date_data SET data_bytes = REPLACE(data_bytes, '#', '1'      ) ;
  99.  
  100.  
  101. CREATE temp TABLE blobdata(
  102.   id INTEGER,
  103.   pos INTEGER,
  104.   b1 INTEGER,
  105.   b2 INTEGER,
  106.   b3 INTEGER,
  107.   he INTEGER
  108. );
  109.  
  110. WITH hexrec(i,l,r,c1,c2,c3,h,h11,h12) AS (
  111.   SELECT DATE,0,data_bytes,'','','',hex(data_hr),'','' FROM date_data
  112.   UNION ALL
  113.  
  114.   SELECT i,
  115.   l+1 AS l,
  116.   substr(r,25),
  117.   substr(r,1,8),
  118.   substr(r,9,8),
  119.   substr(r,17,8),
  120.   substr(h,3),
  121.   substr(h,1,1),
  122.   substr(h,2,1)
  123.   FROM hexrec
  124.   WHERE LENGTH(r) > 0
  125. )
  126.  
  127. INSERT INTO blobdata
  128. SELECT i,l,
  129. CASE c1 WHEN '00000000' THEN 0 WHEN '00000001' THEN 1 WHEN '00000010' THEN 2 WHEN '00000011' THEN 3 WHEN '00000100' THEN 4 WHEN '00000101' THEN 5 WHEN '00000110' THEN 6 WHEN '00000111' THEN 7 WHEN '00001000' THEN 8 WHEN '00001001' THEN 9 WHEN '00001010' THEN 10 WHEN '00001011' THEN 11 WHEN '00001100' THEN 12 WHEN '00001101' THEN 13 WHEN '00001110' THEN 14 WHEN '00001111' THEN 15 WHEN '00010000' THEN 16 WHEN '00010001' THEN 17 WHEN '00010010' THEN 18 WHEN '00010011' THEN 19 WHEN '00010100' THEN 20 WHEN '00010101' THEN 21 WHEN '00010110' THEN 22 WHEN '00010111' THEN 23 WHEN '00011000' THEN 24 WHEN '00011001' THEN 25 WHEN '00011010' THEN 26 WHEN '00011011' THEN 27 WHEN '00011100' THEN 28 WHEN '00011101' THEN 29 WHEN '00011110' THEN 30 WHEN '00011111' THEN 31 WHEN '00100000' THEN 32 WHEN '00100001' THEN 33 WHEN '00100010' THEN 34 WHEN '00100011' THEN 35 WHEN '00100100' THEN 36 WHEN '00100101' THEN 37 WHEN '00100110' THEN 38 WHEN '00100111' THEN 39 WHEN '00101000' THEN 40 WHEN '00101001' THEN 41 WHEN '00101010' THEN 42 WHEN '00101011' THEN 43 WHEN '00101100' THEN 44 WHEN '00101101' THEN 45 WHEN '00101110' THEN 46 WHEN '00101111' THEN 47 WHEN '00110000' THEN 48 WHEN '00110001' THEN 49 WHEN '00110010' THEN 50 WHEN '00110011' THEN 51 WHEN '00110100' THEN 52 WHEN '00110101' THEN 53 WHEN '00110110' THEN 54 WHEN '00110111' THEN 55 WHEN '00111000' THEN 56 WHEN '00111001' THEN 57 WHEN '00111010' THEN 58 WHEN '00111011' THEN 59 WHEN '00111100' THEN 60 WHEN '00111101' THEN 61 WHEN '00111110' THEN 62 WHEN '00111111' THEN 63 WHEN '01000000' THEN 64 WHEN '01000001' THEN 65 WHEN '01000010' THEN 66 WHEN '01000011' THEN 67 WHEN '01000100' THEN 68 WHEN '01000101' THEN 69 WHEN '01000110' THEN 70 WHEN '01000111' THEN 71 WHEN '01001000' THEN 72 WHEN '01001001' THEN 73 WHEN '01001010' THEN 74 WHEN '01001011' THEN 75 WHEN '01001100' THEN 76 WHEN '01001101' THEN 77 WHEN '01001110' THEN 78 WHEN '01001111' THEN 79 WHEN '01010000' THEN 80 WHEN '01010001' THEN 81 WHEN '01010010' THEN 82 WHEN '01010011' THEN 83 WHEN '01010100' THEN 84 WHEN '01010101' THEN 85 WHEN '01010110' THEN 86 WHEN '01010111' THEN 87 WHEN '01011000' THEN 88 WHEN '01011001' THEN 89 WHEN '01011010' THEN 90 WHEN '01011011' THEN 91 WHEN '01011100' THEN 92 WHEN '01011101' THEN 93 WHEN '01011110' THEN 94 WHEN '01011111' THEN 95 WHEN '01100000' THEN 96 WHEN '01100001' THEN 97 WHEN '01100010' THEN 98 WHEN '01100011' THEN 99 WHEN '01100100' THEN 100 WHEN '01100101' THEN 101 WHEN '01100110' THEN 102 WHEN '01100111' THEN 103 WHEN '01101000' THEN 104 WHEN '01101001' THEN 105 WHEN '01101010' THEN 106 WHEN '01101011' THEN 107 WHEN '01101100' THEN 108 WHEN '01101101' THEN 109 WHEN '01101110' THEN 110 WHEN '01101111' THEN 111 WHEN '01110000' THEN 112 WHEN '01110001' THEN 113 WHEN '01110010' THEN 114 WHEN '01110011' THEN 115 WHEN '01110100' THEN 116 WHEN '01110101' THEN 117 WHEN '01110110' THEN 118 WHEN '01110111' THEN 119 WHEN '01111000' THEN 120 WHEN '01111001' THEN 121 WHEN '01111010' THEN 122 WHEN '01111011' THEN 123 WHEN '01111100' THEN 124 WHEN '01111101' THEN 125 WHEN '01111110' THEN 126 WHEN '01111111' THEN 127 WHEN '10000000' THEN 128 WHEN '10000001' THEN 129 WHEN '10000010' THEN 130 WHEN '10000011' THEN 131 WHEN '10000100' THEN 132 WHEN '10000101' THEN 133 WHEN '10000110' THEN 134 WHEN '10000111' THEN 135 WHEN '10001000' THEN 136 WHEN '10001001' THEN 137 WHEN '10001010' THEN 138 WHEN '10001011' THEN 139 WHEN '10001100' THEN 140 WHEN '10001101' THEN 141 WHEN '10001110' THEN 142 WHEN '10001111' THEN 143 WHEN '10010000' THEN 144 WHEN '10010001' THEN 145 WHEN '10010010' THEN 146 WHEN '10010011' THEN 147 WHEN '10010100' THEN 148 WHEN '10010101' THEN 149 WHEN '10010110' THEN 150 WHEN '10010111' THEN 151 WHEN '10011000' THEN 152 WHEN '10011001' THEN 153 WHEN '10011010' THEN 154 WHEN '10011011' THEN 155 WHEN '10011100' THEN 156 WHEN '10011101' THEN 157 WHEN '10011110' THEN 158 WHEN '10011111' THEN 159 WHEN '10100000' THEN 160 WHEN '10100001' THEN 161 WHEN '10100010' THEN 162 WHEN '10100011' THEN 163 WHEN '10100100' THEN 164 WHEN '10100101' THEN 165 WHEN '10100110' THEN 166 WHEN '10100111' THEN 167 WHEN '10101000' THEN 168 WHEN '10101001' THEN 169 WHEN '10101010' THEN 170 WHEN '10101011' THEN 171 WHEN '10101100' THEN 172 WHEN '10101101' THEN 173 WHEN '10101110' THEN 174 WHEN '10101111' THEN 175 WHEN '10110000' THEN 176 WHEN '10110001' THEN 177 WHEN '10110010' THEN 178 WHEN '10110011' THEN 179 WHEN '10110100' THEN 180 WHEN '10110101' THEN 181 WHEN '10110110' THEN 182 WHEN '10110111' THEN 183 WHEN '10111000' THEN 184 WHEN '10111001' THEN 185 WHEN '10111010' THEN 186 WHEN '10111011' THEN 187 WHEN '10111100' THEN 188 WHEN '10111101' THEN 189 WHEN '10111110' THEN 190 WHEN '10111111' THEN 191 WHEN '11000000' THEN 192 WHEN '11000001' THEN 193 WHEN '11000010' THEN 194 WHEN '11000011' THEN 195 WHEN '11000100' THEN 196 WHEN '11000101' THEN 197 WHEN '11000110' THEN 198 WHEN '11000111' THEN 199 WHEN '11001000' THEN 200 WHEN '11001001' THEN 201 WHEN '11001010' THEN 202 WHEN '11001011' THEN 203 WHEN '11001100' THEN 204 WHEN '11001101' THEN 205 WHEN '11001110' THEN 206 WHEN '11001111' THEN 207 WHEN '11010000' THEN 208 WHEN '11010001' THEN 209 WHEN '11010010' THEN 210 WHEN '11010011' THEN 211 WHEN '11010100' THEN 212 WHEN '11010101' THEN 213 WHEN '11010110' THEN 214 WHEN '11010111' THEN 215 WHEN '11011000' THEN 216 WHEN '11011001' THEN 217 WHEN '11011010' THEN 218 WHEN '11011011' THEN 219 WHEN '11011100' THEN 220 WHEN '11011101' THEN 221 WHEN '11011110' THEN 222 WHEN '11011111' THEN 223 WHEN '11100000' THEN 224 WHEN '11100001' THEN 225 WHEN '11100010' THEN 226 WHEN '11100011' THEN 227 WHEN '11100100' THEN 228 WHEN '11100101' THEN 229 WHEN '11100110' THEN 230 WHEN '11100111' THEN 231 WHEN '11101000' THEN 232 WHEN '11101001' THEN 233 WHEN '11101010' THEN 234 WHEN '11101011' THEN 235 WHEN '11101100' THEN 236 WHEN '11101101' THEN 237 WHEN '11101110' THEN 238 WHEN '11101111' THEN 239 WHEN '11110000' THEN 240 WHEN '11110001' THEN 241 WHEN '11110010' THEN 242 WHEN '11110011' THEN 243 WHEN '11110100' THEN 244 WHEN '11110101' THEN 245 WHEN '11110110' THEN 246 WHEN '11110111' THEN 247 WHEN '11111000' THEN 248 WHEN '11111001' THEN 249 WHEN '11111010' THEN 250 WHEN '11111011' THEN 251 WHEN '11111100' THEN 252 WHEN '11111101' THEN 253 WHEN '11111110' THEN 254 WHEN '11111111' THEN 255 END,
  130. CASE c2 WHEN '00000000' THEN 0 WHEN '00000001' THEN 1 WHEN '00000010' THEN 2 WHEN '00000011' THEN 3 WHEN '00000100' THEN 4 WHEN '00000101' THEN 5 WHEN '00000110' THEN 6 WHEN '00000111' THEN 7 WHEN '00001000' THEN 8 WHEN '00001001' THEN 9 WHEN '00001010' THEN 10 WHEN '00001011' THEN 11 WHEN '00001100' THEN 12 WHEN '00001101' THEN 13 WHEN '00001110' THEN 14 WHEN '00001111' THEN 15 WHEN '00010000' THEN 16 WHEN '00010001' THEN 17 WHEN '00010010' THEN 18 WHEN '00010011' THEN 19 WHEN '00010100' THEN 20 WHEN '00010101' THEN 21 WHEN '00010110' THEN 22 WHEN '00010111' THEN 23 WHEN '00011000' THEN 24 WHEN '00011001' THEN 25 WHEN '00011010' THEN 26 WHEN '00011011' THEN 27 WHEN '00011100' THEN 28 WHEN '00011101' THEN 29 WHEN '00011110' THEN 30 WHEN '00011111' THEN 31 WHEN '00100000' THEN 32 WHEN '00100001' THEN 33 WHEN '00100010' THEN 34 WHEN '00100011' THEN 35 WHEN '00100100' THEN 36 WHEN '00100101' THEN 37 WHEN '00100110' THEN 38 WHEN '00100111' THEN 39 WHEN '00101000' THEN 40 WHEN '00101001' THEN 41 WHEN '00101010' THEN 42 WHEN '00101011' THEN 43 WHEN '00101100' THEN 44 WHEN '00101101' THEN 45 WHEN '00101110' THEN 46 WHEN '00101111' THEN 47 WHEN '00110000' THEN 48 WHEN '00110001' THEN 49 WHEN '00110010' THEN 50 WHEN '00110011' THEN 51 WHEN '00110100' THEN 52 WHEN '00110101' THEN 53 WHEN '00110110' THEN 54 WHEN '00110111' THEN 55 WHEN '00111000' THEN 56 WHEN '00111001' THEN 57 WHEN '00111010' THEN 58 WHEN '00111011' THEN 59 WHEN '00111100' THEN 60 WHEN '00111101' THEN 61 WHEN '00111110' THEN 62 WHEN '00111111' THEN 63 WHEN '01000000' THEN 64 WHEN '01000001' THEN 65 WHEN '01000010' THEN 66 WHEN '01000011' THEN 67 WHEN '01000100' THEN 68 WHEN '01000101' THEN 69 WHEN '01000110' THEN 70 WHEN '01000111' THEN 71 WHEN '01001000' THEN 72 WHEN '01001001' THEN 73 WHEN '01001010' THEN 74 WHEN '01001011' THEN 75 WHEN '01001100' THEN 76 WHEN '01001101' THEN 77 WHEN '01001110' THEN 78 WHEN '01001111' THEN 79 WHEN '01010000' THEN 80 WHEN '01010001' THEN 81 WHEN '01010010' THEN 82 WHEN '01010011' THEN 83 WHEN '01010100' THEN 84 WHEN '01010101' THEN 85 WHEN '01010110' THEN 86 WHEN '01010111' THEN 87 WHEN '01011000' THEN 88 WHEN '01011001' THEN 89 WHEN '01011010' THEN 90 WHEN '01011011' THEN 91 WHEN '01011100' THEN 92 WHEN '01011101' THEN 93 WHEN '01011110' THEN 94 WHEN '01011111' THEN 95 WHEN '01100000' THEN 96 WHEN '01100001' THEN 97 WHEN '01100010' THEN 98 WHEN '01100011' THEN 99 WHEN '01100100' THEN 100 WHEN '01100101' THEN 101 WHEN '01100110' THEN 102 WHEN '01100111' THEN 103 WHEN '01101000' THEN 104 WHEN '01101001' THEN 105 WHEN '01101010' THEN 106 WHEN '01101011' THEN 107 WHEN '01101100' THEN 108 WHEN '01101101' THEN 109 WHEN '01101110' THEN 110 WHEN '01101111' THEN 111 WHEN '01110000' THEN 112 WHEN '01110001' THEN 113 WHEN '01110010' THEN 114 WHEN '01110011' THEN 115 WHEN '01110100' THEN 116 WHEN '01110101' THEN 117 WHEN '01110110' THEN 118 WHEN '01110111' THEN 119 WHEN '01111000' THEN 120 WHEN '01111001' THEN 121 WHEN '01111010' THEN 122 WHEN '01111011' THEN 123 WHEN '01111100' THEN 124 WHEN '01111101' THEN 125 WHEN '01111110' THEN 126 WHEN '01111111' THEN 127 WHEN '10000000' THEN 128 WHEN '10000001' THEN 129 WHEN '10000010' THEN 130 WHEN '10000011' THEN 131 WHEN '10000100' THEN 132 WHEN '10000101' THEN 133 WHEN '10000110' THEN 134 WHEN '10000111' THEN 135 WHEN '10001000' THEN 136 WHEN '10001001' THEN 137 WHEN '10001010' THEN 138 WHEN '10001011' THEN 139 WHEN '10001100' THEN 140 WHEN '10001101' THEN 141 WHEN '10001110' THEN 142 WHEN '10001111' THEN 143 WHEN '10010000' THEN 144 WHEN '10010001' THEN 145 WHEN '10010010' THEN 146 WHEN '10010011' THEN 147 WHEN '10010100' THEN 148 WHEN '10010101' THEN 149 WHEN '10010110' THEN 150 WHEN '10010111' THEN 151 WHEN '10011000' THEN 152 WHEN '10011001' THEN 153 WHEN '10011010' THEN 154 WHEN '10011011' THEN 155 WHEN '10011100' THEN 156 WHEN '10011101' THEN 157 WHEN '10011110' THEN 158 WHEN '10011111' THEN 159 WHEN '10100000' THEN 160 WHEN '10100001' THEN 161 WHEN '10100010' THEN 162 WHEN '10100011' THEN 163 WHEN '10100100' THEN 164 WHEN '10100101' THEN 165 WHEN '10100110' THEN 166 WHEN '10100111' THEN 167 WHEN '10101000' THEN 168 WHEN '10101001' THEN 169 WHEN '10101010' THEN 170 WHEN '10101011' THEN 171 WHEN '10101100' THEN 172 WHEN '10101101' THEN 173 WHEN '10101110' THEN 174 WHEN '10101111' THEN 175 WHEN '10110000' THEN 176 WHEN '10110001' THEN 177 WHEN '10110010' THEN 178 WHEN '10110011' THEN 179 WHEN '10110100' THEN 180 WHEN '10110101' THEN 181 WHEN '10110110' THEN 182 WHEN '10110111' THEN 183 WHEN '10111000' THEN 184 WHEN '10111001' THEN 185 WHEN '10111010' THEN 186 WHEN '10111011' THEN 187 WHEN '10111100' THEN 188 WHEN '10111101' THEN 189 WHEN '10111110' THEN 190 WHEN '10111111' THEN 191 WHEN '11000000' THEN 192 WHEN '11000001' THEN 193 WHEN '11000010' THEN 194 WHEN '11000011' THEN 195 WHEN '11000100' THEN 196 WHEN '11000101' THEN 197 WHEN '11000110' THEN 198 WHEN '11000111' THEN 199 WHEN '11001000' THEN 200 WHEN '11001001' THEN 201 WHEN '11001010' THEN 202 WHEN '11001011' THEN 203 WHEN '11001100' THEN 204 WHEN '11001101' THEN 205 WHEN '11001110' THEN 206 WHEN '11001111' THEN 207 WHEN '11010000' THEN 208 WHEN '11010001' THEN 209 WHEN '11010010' THEN 210 WHEN '11010011' THEN 211 WHEN '11010100' THEN 212 WHEN '11010101' THEN 213 WHEN '11010110' THEN 214 WHEN '11010111' THEN 215 WHEN '11011000' THEN 216 WHEN '11011001' THEN 217 WHEN '11011010' THEN 218 WHEN '11011011' THEN 219 WHEN '11011100' THEN 220 WHEN '11011101' THEN 221 WHEN '11011110' THEN 222 WHEN '11011111' THEN 223 WHEN '11100000' THEN 224 WHEN '11100001' THEN 225 WHEN '11100010' THEN 226 WHEN '11100011' THEN 227 WHEN '11100100' THEN 228 WHEN '11100101' THEN 229 WHEN '11100110' THEN 230 WHEN '11100111' THEN 231 WHEN '11101000' THEN 232 WHEN '11101001' THEN 233 WHEN '11101010' THEN 234 WHEN '11101011' THEN 235 WHEN '11101100' THEN 236 WHEN '11101101' THEN 237 WHEN '11101110' THEN 238 WHEN '11101111' THEN 239 WHEN '11110000' THEN 240 WHEN '11110001' THEN 241 WHEN '11110010' THEN 242 WHEN '11110011' THEN 243 WHEN '11110100' THEN 244 WHEN '11110101' THEN 245 WHEN '11110110' THEN 246 WHEN '11110111' THEN 247 WHEN '11111000' THEN 248 WHEN '11111001' THEN 249 WHEN '11111010' THEN 250 WHEN '11111011' THEN 251 WHEN '11111100' THEN 252 WHEN '11111101' THEN 253 WHEN '11111110' THEN 254 WHEN '11111111' THEN 255 END,
  131. CASE c3 WHEN '00000000' THEN 0 WHEN '00000001' THEN 1 WHEN '00000010' THEN 2 WHEN '00000011' THEN 3 WHEN '00000100' THEN 4 WHEN '00000101' THEN 5 WHEN '00000110' THEN 6 WHEN '00000111' THEN 7 WHEN '00001000' THEN 8 WHEN '00001001' THEN 9 WHEN '00001010' THEN 10 WHEN '00001011' THEN 11 WHEN '00001100' THEN 12 WHEN '00001101' THEN 13 WHEN '00001110' THEN 14 WHEN '00001111' THEN 15 WHEN '00010000' THEN 16 WHEN '00010001' THEN 17 WHEN '00010010' THEN 18 WHEN '00010011' THEN 19 WHEN '00010100' THEN 20 WHEN '00010101' THEN 21 WHEN '00010110' THEN 22 WHEN '00010111' THEN 23 WHEN '00011000' THEN 24 WHEN '00011001' THEN 25 WHEN '00011010' THEN 26 WHEN '00011011' THEN 27 WHEN '00011100' THEN 28 WHEN '00011101' THEN 29 WHEN '00011110' THEN 30 WHEN '00011111' THEN 31 WHEN '00100000' THEN 32 WHEN '00100001' THEN 33 WHEN '00100010' THEN 34 WHEN '00100011' THEN 35 WHEN '00100100' THEN 36 WHEN '00100101' THEN 37 WHEN '00100110' THEN 38 WHEN '00100111' THEN 39 WHEN '00101000' THEN 40 WHEN '00101001' THEN 41 WHEN '00101010' THEN 42 WHEN '00101011' THEN 43 WHEN '00101100' THEN 44 WHEN '00101101' THEN 45 WHEN '00101110' THEN 46 WHEN '00101111' THEN 47 WHEN '00110000' THEN 48 WHEN '00110001' THEN 49 WHEN '00110010' THEN 50 WHEN '00110011' THEN 51 WHEN '00110100' THEN 52 WHEN '00110101' THEN 53 WHEN '00110110' THEN 54 WHEN '00110111' THEN 55 WHEN '00111000' THEN 56 WHEN '00111001' THEN 57 WHEN '00111010' THEN 58 WHEN '00111011' THEN 59 WHEN '00111100' THEN 60 WHEN '00111101' THEN 61 WHEN '00111110' THEN 62 WHEN '00111111' THEN 63 WHEN '01000000' THEN 64 WHEN '01000001' THEN 65 WHEN '01000010' THEN 66 WHEN '01000011' THEN 67 WHEN '01000100' THEN 68 WHEN '01000101' THEN 69 WHEN '01000110' THEN 70 WHEN '01000111' THEN 71 WHEN '01001000' THEN 72 WHEN '01001001' THEN 73 WHEN '01001010' THEN 74 WHEN '01001011' THEN 75 WHEN '01001100' THEN 76 WHEN '01001101' THEN 77 WHEN '01001110' THEN 78 WHEN '01001111' THEN 79 WHEN '01010000' THEN 80 WHEN '01010001' THEN 81 WHEN '01010010' THEN 82 WHEN '01010011' THEN 83 WHEN '01010100' THEN 84 WHEN '01010101' THEN 85 WHEN '01010110' THEN 86 WHEN '01010111' THEN 87 WHEN '01011000' THEN 88 WHEN '01011001' THEN 89 WHEN '01011010' THEN 90 WHEN '01011011' THEN 91 WHEN '01011100' THEN 92 WHEN '01011101' THEN 93 WHEN '01011110' THEN 94 WHEN '01011111' THEN 95 WHEN '01100000' THEN 96 WHEN '01100001' THEN 97 WHEN '01100010' THEN 98 WHEN '01100011' THEN 99 WHEN '01100100' THEN 100 WHEN '01100101' THEN 101 WHEN '01100110' THEN 102 WHEN '01100111' THEN 103 WHEN '01101000' THEN 104 WHEN '01101001' THEN 105 WHEN '01101010' THEN 106 WHEN '01101011' THEN 107 WHEN '01101100' THEN 108 WHEN '01101101' THEN 109 WHEN '01101110' THEN 110 WHEN '01101111' THEN 111 WHEN '01110000' THEN 112 WHEN '01110001' THEN 113 WHEN '01110010' THEN 114 WHEN '01110011' THEN 115 WHEN '01110100' THEN 116 WHEN '01110101' THEN 117 WHEN '01110110' THEN 118 WHEN '01110111' THEN 119 WHEN '01111000' THEN 120 WHEN '01111001' THEN 121 WHEN '01111010' THEN 122 WHEN '01111011' THEN 123 WHEN '01111100' THEN 124 WHEN '01111101' THEN 125 WHEN '01111110' THEN 126 WHEN '01111111' THEN 127 WHEN '10000000' THEN 128 WHEN '10000001' THEN 129 WHEN '10000010' THEN 130 WHEN '10000011' THEN 131 WHEN '10000100' THEN 132 WHEN '10000101' THEN 133 WHEN '10000110' THEN 134 WHEN '10000111' THEN 135 WHEN '10001000' THEN 136 WHEN '10001001' THEN 137 WHEN '10001010' THEN 138 WHEN '10001011' THEN 139 WHEN '10001100' THEN 140 WHEN '10001101' THEN 141 WHEN '10001110' THEN 142 WHEN '10001111' THEN 143 WHEN '10010000' THEN 144 WHEN '10010001' THEN 145 WHEN '10010010' THEN 146 WHEN '10010011' THEN 147 WHEN '10010100' THEN 148 WHEN '10010101' THEN 149 WHEN '10010110' THEN 150 WHEN '10010111' THEN 151 WHEN '10011000' THEN 152 WHEN '10011001' THEN 153 WHEN '10011010' THEN 154 WHEN '10011011' THEN 155 WHEN '10011100' THEN 156 WHEN '10011101' THEN 157 WHEN '10011110' THEN 158 WHEN '10011111' THEN 159 WHEN '10100000' THEN 160 WHEN '10100001' THEN 161 WHEN '10100010' THEN 162 WHEN '10100011' THEN 163 WHEN '10100100' THEN 164 WHEN '10100101' THEN 165 WHEN '10100110' THEN 166 WHEN '10100111' THEN 167 WHEN '10101000' THEN 168 WHEN '10101001' THEN 169 WHEN '10101010' THEN 170 WHEN '10101011' THEN 171 WHEN '10101100' THEN 172 WHEN '10101101' THEN 173 WHEN '10101110' THEN 174 WHEN '10101111' THEN 175 WHEN '10110000' THEN 176 WHEN '10110001' THEN 177 WHEN '10110010' THEN 178 WHEN '10110011' THEN 179 WHEN '10110100' THEN 180 WHEN '10110101' THEN 181 WHEN '10110110' THEN 182 WHEN '10110111' THEN 183 WHEN '10111000' THEN 184 WHEN '10111001' THEN 185 WHEN '10111010' THEN 186 WHEN '10111011' THEN 187 WHEN '10111100' THEN 188 WHEN '10111101' THEN 189 WHEN '10111110' THEN 190 WHEN '10111111' THEN 191 WHEN '11000000' THEN 192 WHEN '11000001' THEN 193 WHEN '11000010' THEN 194 WHEN '11000011' THEN 195 WHEN '11000100' THEN 196 WHEN '11000101' THEN 197 WHEN '11000110' THEN 198 WHEN '11000111' THEN 199 WHEN '11001000' THEN 200 WHEN '11001001' THEN 201 WHEN '11001010' THEN 202 WHEN '11001011' THEN 203 WHEN '11001100' THEN 204 WHEN '11001101' THEN 205 WHEN '11001110' THEN 206 WHEN '11001111' THEN 207 WHEN '11010000' THEN 208 WHEN '11010001' THEN 209 WHEN '11010010' THEN 210 WHEN '11010011' THEN 211 WHEN '11010100' THEN 212 WHEN '11010101' THEN 213 WHEN '11010110' THEN 214 WHEN '11010111' THEN 215 WHEN '11011000' THEN 216 WHEN '11011001' THEN 217 WHEN '11011010' THEN 218 WHEN '11011011' THEN 219 WHEN '11011100' THEN 220 WHEN '11011101' THEN 221 WHEN '11011110' THEN 222 WHEN '11011111' THEN 223 WHEN '11100000' THEN 224 WHEN '11100001' THEN 225 WHEN '11100010' THEN 226 WHEN '11100011' THEN 227 WHEN '11100100' THEN 228 WHEN '11100101' THEN 229 WHEN '11100110' THEN 230 WHEN '11100111' THEN 231 WHEN '11101000' THEN 232 WHEN '11101001' THEN 233 WHEN '11101010' THEN 234 WHEN '11101011' THEN 235 WHEN '11101100' THEN 236 WHEN '11101101' THEN 237 WHEN '11101110' THEN 238 WHEN '11101111' THEN 239 WHEN '11110000' THEN 240 WHEN '11110001' THEN 241 WHEN '11110010' THEN 242 WHEN '11110011' THEN 243 WHEN '11110100' THEN 244 WHEN '11110101' THEN 245 WHEN '11110110' THEN 246 WHEN '11110111' THEN 247 WHEN '11111000' THEN 248 WHEN '11111001' THEN 249 WHEN '11111010' THEN 250 WHEN '11111011' THEN 251 WHEN '11111100' THEN 252 WHEN '11111101' THEN 253 WHEN '11111110' THEN 254 WHEN '11111111' THEN 255 END,
  132. CASE h11 WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3' THEN 3 WHEN '4' THEN 4 WHEN '5' THEN 5 WHEN '6' THEN 6 WHEN '7' THEN 7 WHEN '8' THEN 8 WHEN '9' THEN 9 WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE 0 END * 16 +
  133. CASE h12 WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3' THEN 3 WHEN '4' THEN 4 WHEN '5' THEN 5 WHEN '6' THEN 6 WHEN '7' THEN 7 WHEN '8' THEN 8 WHEN '9' THEN 9 WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE 0 END
  134. FROM hexrec rt
  135. WHERE l > 0;
  136.  
  137. CREATE INDEX b1 ON blobdata(id,pos);
  138.  
  139. .header ON
  140. .mode csv
  141. .output extract_raw_hr2.csv
  142. SELECT TIME,description,steps
  143. ,round(iDistance,2) AS WalkDistance
  144. ,CASE WHEN runs> 0 THEN Round(((3+runs*2) * iDistance / 15),2) ELSE 0 END AS RunDistance
  145. ,CASE WHEN steps > 0 THEN Round((weight *2.2046 *  iDistance * (SELECT COALESCE(MAX(Cal),0.95)/COALESCE(MAX(Dist),40.233) FROM _CalList WHERE Dist <= iDistance) / 60),2) ELSE 0 END AS WalkCalories
  146. ,CASE WHEN runs> 0 THEN Round(((3+runs*2.0) /15 * weight *2.2046 *  iDistance * (SELECT COALESCE(MAX(Cal),0.95)/COALESCE(MAX(Dist),40.233) FROM _CalList WHERE Dist <= iDistance) / 60),2) ELSE 0 END AS RunCalories
  147. ,RawActivity,RawSensorData,HeartRate
  148.  
  149.  FROM (
  150. SELECT datetime(d.DATE,'+'||CAST(pos AS text)||' minute') AS TIME
  151. ,CASE WHEN b1 IN (4,5) THEN 'Sleep' WHEN b1 > 15 AND b3 > 0 THEN 'Run' WHEN b1 > 0 AND b3 > 0 THEN 'Walk' ELSE 'Idle' END AS Description
  152. ,CASE WHEN b1 NOT IN (4,5) THEN b3 ELSE 0 END AS Steps
  153. ,b1 AS RawActivity
  154. ,b2 AS RawSensorData
  155. ,CASE WHEN b3 > 0 THEN (b1 >> 4) ELSE 0 END AS Runs
  156. --,case when b3 > 0 then (b1 & 0xf) else 0 end as mode
  157. ,CASE WHEN b3> 0 AND b1 NOT IN (4,5) THEN CASE WHEN b3 <= 90 THEN b3*h1*0.9 ELSE b3*b3*h1/(CASE WHEN b3 > 120 THEN 125 ELSE 100 END) END ELSE 0 END AS iDistance
  158. ,CASE WHEN he NOT IN (255,254) THEN he ELSE 0 END AS HeartRate
  159.  
  160. FROM blobdata b, date_data d,_Hght
  161. WHERE d.DATE = b.id
  162. AND b.b1 <> 126
  163. ORDER BY b.id, pos
  164. ) a
  165. , _PersonParams;
  166.  
  167. .output extract_raw_summary_hr2.csv
  168.  
  169. SELECT TIME
  170. ,SUM(Steps) AS Steps
  171. ,SUM(Runs) AS Runs
  172. ,round(SUM(iDistance)) AS WalkDistance
  173. ,round(SUM(CASE WHEN runs>0 THEN (3+runs*2) * iDistance / 15 ELSE 0 END)) AS RunDistance
  174. ,round(SUM(weight *2.2046 *  iDistance * (SELECT COALESCE(MAX(Cal),0.95)/COALESCE(MAX(Dist),40.233) FROM _CalList WHERE Dist <= iDistance) / 60)) AS WalkCalories
  175. ,round(SUM(CASE WHEN runs> 0 THEN (3+runs*2.0) /15 * weight *2.2046 *  iDistance * (SELECT COALESCE(MAX(Cal),0.95)/COALESCE(MAX(Dist),40.233) FROM _CalList WHERE Dist <= iDistance) / 60 ELSE 0 END)) AS RunCalories
  176.  
  177.  FROM
  178.  (SELECT d.DATE AS TIME
  179. ,CASE WHEN b1 IN (4,5) THEN 'Sleep' WHEN b1 > 15 AND b3 > 0 THEN 'Run' WHEN b1 > 0 AND b3 > 0 THEN 'Walk' ELSE 'Idle' END AS Description
  180. ,CASE WHEN b1 NOT IN (4,5) THEN b3 ELSE 0 END AS Steps
  181. ,b1 AS RawActivity,b2 AS RawSensorData
  182. ,CASE WHEN b3 > 0 THEN (b1 >> 4) ELSE 0 END AS Runs
  183. --,case when b3 > 0 then (b1 & 0xf) else 0 end as mode
  184. ,CASE WHEN b3> 0 AND b1 NOT IN (4,5) THEN CASE WHEN b3 <= 90 THEN b3*h1*0.9 ELSE b3*b3*h1/(CASE WHEN b3 > 120 THEN 125 ELSE 100 END) END ELSE 0 END AS iDistance
  185.  
  186. FROM blobdata b, date_data d,_Hght
  187. WHERE d.DATE = b.id
  188. AND b.b1 <> 126
  189. AND b3 > 0
  190. AND (b1 & 0xf) <> 6
  191. AND (b1 & 0xf) <= 7
  192. AND (b1 & 0xf) > 0
  193. AND b1 NOT IN (4,5)
  194. ) a, _PersonParams
  195. GROUP BY TIME;
  196.  
  197. .output extract_hr2.csv
  198.  
  199. SELECT device_id AS ID
  200. ,datetime(TIME, 'unixepoch') AS Datum
  201. ,hr AS HeartRate
  202. FROM heart_rate
  203. GROUP BY ID;
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!