Untitled

mail@pastecode.io avatar
unknown
plain_text
2 months ago
7.8 kB
2
Indexable
Never
SELECT HEX(t.auction_campaign_id) AS `campaignId`,
       HEX(product_id) AS `productId`,
       MAX(t.moving_average) AS max_moving_average
FROM (
         SELECT
             AVG(count_id) AS moving_average,
             auction_campaign_id,
             product_id
         FROM (
                  SELECT
                      DATE_FORMAT(p.timestamp, '%Y-%m-%d %H:00:00') AS hour,
                      COUNT(p.id) AS count_id,
                      p.auction_campaign_id,
                      product_id
                  FROM auction_campaign_point AS p
                  WHERE p.timestamp > (NOW() - INTERVAL 6 HOUR)
                    AND p.type = 'draw_detailed'
                    AND p.auction_campaign_id IN (x'11ec2344fc76c8b48b85ae2728cf7a26', x'11ed305c1fcc5d18b344322c233f937a', x'11ed3fddd6a54d52af321213a2fba45e', x'11ed3fddfb4e2a8eb39c26706556da14', x'11ed3fde673b439eae9bba7071cf746d', x'11ed3fe72456f8128d6cd68e360bb95d', x'11ed4f1c7cc3885ab62ffa4a8451d2ad', x'11ed4f955164502697ba12f3943a2b8c', x'11ed4f9581f84b34a955c65fe310dd25', x'11ed4f9632da990caeec6a3279f20252', x'11ed522af9b65606a3418e7ccf097821', x'11ed5a71eb757fd2a0020ec0f7ce6afa', x'11ed5b6d54497292ae1b361dd904ba1b', x'11ed5d0e5afe2072910d623e4d76ff47', x'11ed5d0e896fa692a611f2f98f288ae2', x'11ed5d0ee78488608255660973b9f7b7', x'11ed61079f1da2eeabbe6ab856e082ec', x'11ed610b1911afca800c6ab856e082ec', x'11ed610b3397652e993cfe40fd6651dc', x'11ed610ba859020a99515ed7007d0742', x'11ed610f283a9a80bc495ed7007d0742', x'11ed612111c4c78cb5d44245e18c8667', x'11ed64098ae8b372b1d03eb1d430fa8b', x'11edaed42fd6e1be8ede32a7fcc9fe61', x'11edaf844f5b217094e9ee7a274cdef5', x'11edafdc186037ca945eb2591e1aab8e', x'11edafdcd588f1debfb59677cd7ca3f3', x'11edafde69b7e5eebd3dca8718d5d45d', x'11edafde7dfd1af6b7f6c2d7a6408a93', x'11edb0524e8ff18496087e46c519acac', x'11edb1288608d082945cdeaaef7dff91', x'11edb128dfd5dd4ebb51d2e3fad366d9', x'11edb129f5d83938b0cdd63bd8fd2cf9', x'11edb12a6614d95e93c01afbabd5ebef', x'11edb12aaaa21492823062e9f77c6008', x'11edb12b3f7d72e69580dabb56eb153d', x'11edb12cfec9de5e9ee5b623368ea1cf', x'11edb12d636396acad0712aefe6d7af1', x'11edb12d94e7d3f0ac35c663431f1fe1', x'11edb12e01c88fe6b0f9d25686524a6e', x'11edb12edbc295988f85d25686524a6e', x'11edb12f1ec2239aa357c6ce9ce1bf2b', x'11edb12f56698e32985eceb37bcf875a', x'11edb12f7c3f3076b4d7aab7e5dc21d9', x'11edb12fc2b9d04c82dbceb37bcf875a', x'11edb1301cfa85ec9d6b0213d5aab225', x'11edb130ad70c41a88a80213d5aab225', x'11edb130ff27cc188bc88e8ac9c3a1bd', x'11edb131273884728d5f1aad50604824', x'11edb13162439228b06cc663431f1fe1', x'11edb131eed15f0e9a5c42a3763c0c0c', x'11edb132dd7cb5e0b88d12aefe6d7af1', x'11edb1337723066891958e8ac9c3a1bd', x'11edb133dac9bbf8b884ceb37bcf875a', x'11edb1346deea632a486f6e00218c38e', x'11edb13565f81660be510213d5aab225', x'11edb1457782381aa22e666e13cefe3f', x'11edb145cad1c7e2b7d5eadb68df56c4', x'11edb145ea940a7c8097b65cc2def01f', x'11edb1463453d9a886e03a6cfb5cf403', x'11edb146692c4ea88a18aa7fdc746c05', x'11edb146a1d05e84ac74bec533cc2df0', x'11edb146d0414e86b84bc6ce9ce1bf2b', x'11edb146ed0d7120922a3a6cfb5cf403', x'11edb1475dc6ecde93fcb623368ea1cf', x'11edb147d17f40a48fba3a6cfb5cf403', x'11edb14810530c709bc982cc187f5f6e', x'11edb14877b80a1e9694a660c234412d', x'11edb148d2a857e4b647ba3903d384b6', x'11edb149528c4042b7e03a6cfb5cf403', x'11edb149e927e7229648d23486e7558d', x'11edb14af773fb8a9529d2e3fad366d9', x'11edb14b394e625cb1124ab5294ff440', x'11edb14bab9036889c94666e13cefe3f', x'11edb1c4203a1d78866c06d10cee0511', x'11edb1c579c8fbecb90fe6a4c535853d', x'11edb1c5ae4c6444aeb8e6a4c535853d', x'11edb1f4222dfd36bf960a66bf952dc0', x'11edb1f4515a10409b293e978f0ee165', x'11edb1f484663a5491156215591dd06a', x'11edb1f5cb5ea0dab0fe669a3cd19d23', x'11edb1f5f91946f68a46ce78a58368fe', x'11edb1f935fd9f1aafe8c637e5e3e4fd', x'11edb1f95905fb608a29aac9646a9bfc', x'11edb1f9848aa6b4a2caaac87993d7fd', x'11edb1f9ae79494492c1ae8de687a7ba', x'11edb1fa05aefc5ea5eef24dcaef44f2', x'11edb1fa568ad2a696112e15f6774eff', x'11edb1faabba1eeeb8de6215591dd06a', x'11edb1fac849a746ae29f2e6bd66e3c9', x'11edb1faf2f420169af6d2e8c835fd28', x'11edb1fb52dba83c96add2e8c835fd28', x'11edb1fb869dfa26a636f2e6bd66e3c9', x'11edb1fbb69fb90892c5d65c7ef9a01d', x'11edb1fc082ae8ceb9f8f24dcaef44f2', x'11edb1fc2d3f3cbeae623e978f0ee165', x'11edb227c68f87e0a39e3a59179359d9', x'11edb2280b2c526697fa3a59179359d9', x'11edb22827464f248957ae8de687a7ba', x'11edb22847c6bd60b7bc2efda29ff28f', x'11edb22955953dda9274f24dcaef44f2', x'11edb231ffc55eeab48516c637d412d9', x'11eec1dbbec52a8c834a9aa2da27e356', x'11eec1dbd99fc1f08a13f2505b7738f9', x'11eec1dc08801e2a9219b6bea4e720d2', x'11eec1dc2acddf448ff5826f25c56db5', x'11eec1de593197dea9d04ad5ea003688', x'11eec1de924a584e8c6cb64119465dc6', x'11eec1dea4f37f84b87242d9a5a4b1b7', x'11eec1dec27b4b90b736fa539d74ca1a', x'11eec1dfeb03dcde846abe507c63e0a7', x'11eec1e00aa011ac9bbf42d9a5a4b1b7', x'11eec1e01c4a67b88bc5cad17e82696a', x'11eec1e07253005293bbf2505b7738f9', x'11eec1e078d8db689bfdd6fb669948ca', x'11eec1e0806540ecb6bd1e11b676c6a7', x'11eec1e090c297d293cffa539d74ca1a', x'11eec1e0b047c3dea7e41e11b676c6a7', x'11eec1e0bd7f2218995ecaf58f721f2f', x'11eec1e0cacf3f02b8ab727fb2b1c66d', x'11eec1e0f27a3156996852a257cef7e4', x'11eec1e10f61cc5cb256b6bea4e720d2', x'11eec1e1224c448cb0a92a2cf6954676', x'11eec1e12e56ea02abce92e8bdd00f1a', x'11eec1e141aa8bf4ab91b6bea4e720d2', x'11eec1e16d2a1e2abb579233454b5de3', x'11eec1e17e95b11a8b6ab64119465dc6', x'11eec1e18d34378c91ff0ad838377c25', x'11eec1e1a3c6d14ea267ea709be81dc6', x'11eec1e1f30b0d8887e8b2fad16b64b1', x'11eec1e209740ec6aa2a0ad838377c25', x'11eec1e21fb8b024a10e92e8bdd00f1a', x'11eec1e232947ade95e0bab55aec0515', x'11eec20c7a38827a8878c28b8910ab3b', x'11eec20c94baa11eafba36318e8b1eee', x'11eec20cc06adc20aa50f6e4ab8c70db', x'11eec20cd1de6eeabdb27abfe8026d4f', x'11eec2959b24b5568c1e8611e304ab3f', x'11eec2969c2de1c48a5106cec613ed02', x'11eec296f922257a87cbcaada36c9ec2', x'11eec297094f149485178ec3e4013a59', x'11eec297387893f8b375a25a65d92a6f', x'11eec2974ac9ed188b1f8262db5ee3d8', x'11eec297606e74e0b1e48ec3e4013a59', x'11eec29784e2ca88a9463e05455d9aff', x'11eec297f0a476909a55aae38787f65d', x'11eec298223452e8a9a5ea812e65a8a5', x'11eec298358da2ea95898611e304ab3f', x'11eec29843ee7ecc8b3c9ee2da7c64d9', x'11eec29d0eb03782a2cad28cb2135ee8', x'11eec29d2f40161699678262db5ee3d8', x'11eec29d49be07a090729ee2da7c64d9', x'11eec29d5f10ac84b37d325131b10aa4', x'11eec29d866cabac80483e05455d9aff', x'11eec29d9b858cd4a61db2f51d6ca91c', x'11eec29daf026f20a2a0fa95c9e62bd9', x'11eec29dc03bac6696285ed636a8554c', x'11eec29e3880d1ceb71bcaada36c9ec2', x'11eec29e4cce5cfa9b5b8611e304ab3f', x'11eec29e60a5c77cb71f3e05455d9aff', x'11eec29e7cbe6eb48f6e2a71a0fdb90b', x'11eec2b415eddda8b64c9ee2da7c64d9', x'11eec2b42b9a98b283319ee2da7c64d9', x'11eec2b44291ed7c8bf35ac20bd60a86', x'11eec2b45969b9a8b1ff9ee2da7c64d9', x'11eec83a156f6f6e8932261292ff45ec', x'11eec83a25ad936a8827fe0398884c10', x'11eec83a3649a9c0a482026b84106327', x'11eec9de4bae13c8883a161a9a7d1a9f', x'11eec9de57b50514a28ece5c401a3c89', x'11eec9de7aee7c5e9a5cd6a57106e769', x'11eeca59d71480acb6328a8eac5f43fb', x'11eeca5a10d0109aab1a16f969155d80', x'11eeca5a2ad447fe9161eeae18b9561d', x'11eeca5a3a5e9120991ceeae18b9561d', x'11eecc257cb9d7b0a74b2eb8b73ada0d', x'11eecc25895f3b8688462a39ac845590', x'11eecc259827bc24bf87a6dac6b8501f', x'11eecc25a59e258299e3122c813dc38a', x'11eecf33f16d44328c84c2f28f14c177', x'11eecf33fe6b2b229fd2d66369bdad80', x'11eecf3408f4266697c9eeea2f340557', x'11eecf34129f95ec9b449204dd36746e', x'11eecf596bf8a1ae8adfdac220484549', x'11eecf59740fc200998aaac9b485f245', x'11eecf598894fd08aac55eb9fd967619', x'11eecf599eda74769ea902aff20700d2')
                  GROUP BY hour, p.auction_campaign_id, p.product_id
              ) AS subquery
         GROUP BY hour, auction_campaign_id
     ) AS t
GROUP BY t.auction_campaign_id;
Leave a Comment