SQLite SELECT рядом стоящие записи

Тема в разделе "Базы данных", создана пользователем papa, 3 окт 2017.

Метки:
Модераторы: latteo
  1. papa

    papa Постоялец

    Регистр.:
    28 сен 2006
    Сообщения:
    148
    Симпатии:
    3
    Вообщем. База SQLite 3. Есть такое:
    CREATE TABLE `ZAnuCu_OnuCAHuE`(
    `id` INTEGER PRIMARY KEY,
    `HAZBAHuE` TEXT ASC,
    `MODELb` TEXT,
    `KPATHOCTb` INTEGER,
    `DOCTynHIOCTb` INTEGER,
    `KAPTuHKA` TEXT,
    `KATEGOPuia` INTEGER,
    `PAZDEL` INTEGER,
    `CTATyC` TEXT,
    `PEDAKTuPOBAHuE` TEXT);

    CREATE TABLE `ZAnuCu_KATEGOPuu`(
    `id` INTEGER ASC,
    `KATEGOPuia` INTEGER ASC,
    `PAZDEL` INTEGER ASC,
    `PEDAKTuPOBAHuE` TEXT);

    CREATE UNIQUE INDEX IF NOT EXISTS `uHDEKC_id` ON `TOBAPbl_OnuCAHuE` (`id`);
    CREATE INDEX IF NOT EXISTS `uHDEKC_HAZBAHuE` ON `TOBAPbl_OnuCAHuE` (`HAZBAHuE`);
    Я пытаюсь получить записи, стоящие рядом с Х.
    Скажем: Есть элемент, куда я вывел список. Прокрутил я его, и тут сработал скрипт. Я даю команду "Дай мне 500 записей до и после Х-ой записи". Так вот. Как сделать это на SQLite?
    Единственное, где я вообще нашёл упоминание об "Select рядом стоящие записи" это http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=261464&msg=2346853 - "Почитай у Кена Хендорсона про оператор Select и рядом стоящие записи.". Вот только я что-то не понял: где это? Может видел это место, но не сообразил "то, что нужно". Прошу помочь разобраться с вопросом.

    Дополнение: цель - сделать "ленивую подргузку, когда ползунок дошёл до места". Т.е. прокрутили до 10%(от конца) - скрипт сработал - список обновился. И сделать это быстро. А значит: повесить на SQLite эту задачу. Собствено я сюда и обратился.


    В базе записи по алфавиту, как и в выводе. Т.е. когда прокрутил - Я знаю из какой категории, и какой по счёту записи(по алфавиту отсортировано в базе "`HAZBAHuE` TEXT ASC," , и в выводе). Также я знаю какие категории идут до и после, и количество записей в каждой. Проблема в реализации запроса.
     
  2. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    155
    Симпатии:
    101
    Сами по себе эти словосочетания - ересь. Мыслите не реляционно. Рано или поздно поймете.

    Показали бы выглядит запрос сейчас.

    А вообще, у вас есть "HAZBAHuE" последней записи, до которой прокручено. Соответственно, нужно отсортировать вверх (до) или вниз (после) и поставить LIMIT 500.

    Что-то типа

    Код:
    WHERE HAZBAHuE < "Ваше последнее название"
    ORDER BY HAZBAHuE DESC
    LIMIT 500
    
    PS. Нашел орфографическую ошибку - "DOCTynHIOCTb" исправьте на "DOCTynHOCTb" :D
     
  3. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    155
    Симпатии:
    101
    Тестовые данные:

    Код:
    INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (1, 'Товар A', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
    INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (2, 'Товар В', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
    INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (3, 'Товар Б', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
    INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (4, 'Товар Д', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
    INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (5, 'Товар Г', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
    
    >> В базе записи по алфавиту.

    Код:
    select * from ZAnuCu_OnuCAHuE;
    
    1|Товар A|Модель|1|1|img/blank.png|1|1|1|
    2|Товар В|Модель|1|1|img/blank.png|1|1|1|
    3|Товар Б|Модель|1|1|img/blank.png|1|1|1|
    4|Товар Д|Модель|1|1|img/blank.png|1|1|1|
    5|Товар Г|Модель|1|1|img/blank.png|1|1|1|
    
    А вот и не по алфавиту. Просто у вас по порядку вставлялось, поэтому так выглядит.

    Так можно получить "до":

    Код:
    SELECT *
    FROM ZAnuCu_OnuCAHuE
    WHERE HAZBAHuE < 'Товар Б'
    ORDER BY HAZBAHuE DESC
    LIMIT 500;
    
    1|Товар A|Модель|1|1|img/blank.png|1|1|1|
    
    Так можно получить "после":

    Код:
    SELECT *
    FROM ZAnuCu_OnuCAHuE
    WHERE HAZBAHuE > 'Товар Б'
    ORDER BY HAZBAHuE ASC
    LIMIT 500;
    
    2|Товар В|Модель|1|1|img/blank.png|1|1|1|
    5|Товар Г|Модель|1|1|img/blank.png|1|1|1|
    4|Товар Д|Модель|1|1|img/blank.png|1|1|1|
    
     
  4. papa

    papa Постоялец

    Регистр.:
    28 сен 2006
    Сообщения:
    148
    Симпатии:
    3
    Поправил! Благодарю.

    С "ORDER BY HAZBAHuE" выводит записи, в тестовой базе, за 7.5 секунд. Без - 50 милисекунд.
    Тестовый запрос:
    SELECT t.`id`,
    t.`HAZBAHuE`,
    t.`MODELb`,
    t.`KPATHOCTb`,
    t.`DOCTynHIOCTb`,
    t.`KAPTuHKA`,
    t_k.`KATEGOPuia`,
    t.`KOLOHKA1239`,
    t.`KOLOHKA1267`,
    t.`KOLOHKA1272`
    FROM
    `ZAnuCu_OnuCAHuE` t,
    `ZAnuCu_KATEGOPuu` t_k
    WHERE t_k.`KATEGOPuia` IN (64, 2680, 29361, 2720, 2656, 2800, 2727, 2612, 2681, 2768, 2729, 29051, 2882, 2750, 2761, 29208, 29209, 2608, 29421, 2791, 2745, 2676, 29419, 2790, 28470, 28472, 28586, 2715, 2749, 24560, 29362, 2596, 29210, 30893, 2734, 2884, 29813, 29946, 2717, 28542, 29427, 29227, 28585, 30228, 28482, 28483, 29416, 29429, 2685, 2880, 2786, 22895, 29435, 2592, 2595, 2590, 2643, 2642, 28547, 22597, 2846, 29844, 2740, 2865, 2598, 2627, 28541, 29428, 29811, 29975, 2647, 2633, 2854, 29955, 29804, 29954, 24988, 2638, 2736, 2883, 2667, 2626, 29727, 2764, 2765, 2655, 28469, 29802, 29962, 29792, 29786, 2738, 22684, 28587, 24722, 2618, 29797, 2847, 2849, 2861, 29231, 29053, 24723, 2624, 22598, 29783, 29417, 29412, 2855, 2856, 2848, 29789, 30046, 28588, 29221, 29234, 30060, 30053, 29800, 29963, 29899, 2763, 2670, 29822, 2683, 2639, 2869, 29935, 29940, 2798, 29782, 29938, 2762, 29226, 2615, 2863, 30240, 30236, 2772, 2775, 2857, 28589, 28600, 28578, 29617, 28551, 2603, 2682, 30048, 30066, 28686, 29212, 2724, 29434, 2755, 29614, 2650, 2671, 2640, 2793, 29729, 2663, 29418, 29422, 28471, 29423, 29430, 2780, 2637, 29411, 2607, 24724, 29232, 2789, 2748, 29027, 2784, 30059, 29414, 28549, 2859, 2787, 2723, 29436, 29831, 29843, 2614, 30040, 2664, 2674, 2728, 2636, 24896, 29441, 29206, 23929, 29941, 2747, 29942, 2737, 2741, 2795, 2732, 28546, 28594, 28596, 30237, 29363, 2823, 2845, 29842, 2646, 2872, 2735, 29214, 2635, 29799, 2866, 29943, 29220, 29219, 30230, 2619, 2773, 28603, 28581, 2653, 2842, 29618, 2873, 2742, 29971, 29834, 29790, 29785, 2691, 28590, 2599, 28604, 28582, 28591, 2644, 2629, 29728, 2666, 2679, 2771, 2660, 30231, 28605, 28583, 2890, 2875, 2889, 24821, 30234, 30894, 29364, 29956, 2593, 2853, 2611, 2776, 2613, 29845, 22894, 2886, 2602, 2725, 29437, 2692, 2634, 2662, 2684, 2652, 29987, 2631, 30051, 30058, 2844, 28544, 29425, 2767, 29285, 2601, 22356, 29431, 2862, 2739, 29616, 30238, 2721, 29432, 2783, 2657, 29819, 29947, 29833, 29968, 2621, 30047, 30065, 2668, 29791, 29224, 2616, 28592, 28606, 28477, 28613, 28609, 28612, 28607, 28593, 28552, 28597, 28610, 28608, 29223, 2610, 2716, 29803, 29964, 30049, 30067, 2782, 29233, 2858, 29217, 2673, 30056, 2594, 29812, 29945, 2609, 2591, 29983, 2870, 30020, 2677, 30039, 29438, 29444, 2759, 30041, 30043, 30042, 2757, 2669, 29218, 29829, 29230, 28598, 28595, 29730, 2718, 2726, 28478, 30235, 29936, 29982, 2605, 2753, 2756, 2876, 24958, 2731, 2722, 29433, 2887, 29827, 29835, 29967, 29973, 2877, 29200, 23653, 2769, 29228, 29814, 29211, 29820, 2885, 29959, 29960, 29961, 30052, 29958, 2751, 29225, 2754, 28545, 29424, 29415, 29426, 2630, 2730, 29229, 29808, 29976, 29830, 29828, 30227, 2622, 29832, 29794, 29951, 30050, 29796, 29952, 29795, 2686, 24626, 2688, 2687, 29818, 29948, 29120, 29816, 29949, 29957, 29787, 30768, 29944, 29207, 24718, 2641, 2785, 29825, 29824, 29826, 2852, 2746, 2719, 28601, 28579, 29823, 29966, 24717, 30064, 29798, 29953, 2743, 29442, 29205, 28479, 2878, 2675, 2774, 29937, 29984, 29986, 28611, 28599, 28577, 2625, 2794, 30057, 2871, 29815, 2632, 2744, 24719, 29817, 29950, 2779, 2604, 2651, 28397, 2648, 29810, 29977, 29939, 2623, 2867, 2597, 2868, 2620, 24561, 29215, 2600, 29439, 2690, 29801, 29965, 30045, 28480, 30061, 30063, 29788, 29793, 29805, 29974, 29809, 29978, 29807, 29979, 2879, 29806, 29980, 2881, 29934, 29981, 2888, 30239, 22682, 30054, 2777, 2778, 30766, 30068, 22683, 2645, 28602, 28580, 2628, 2665, 2678, 2770, 2659, 30229, 2766, 29972, 2733, 28584, 28474, 28481, 28475, 29202, 24017, 29784, 30767, 2654, 29846, 2851, 2781, 29052, 2617, 29284, 28463, 28550, 2658, 30038, 30226, 2661, 24627, 29420, 2792, 2843, 30895, 30055, 2841, 2864, 2860, 30233, 2796, 2797, 22782, 29222, 2850, 30044, 2672, 29213, 29216, 2760, 29443, 29204, 29440, 29203, 29413, 29821, 29985, 29970, 30062, 29836, 29969, 30232, 30037, 29615) AND t_k.`id` = t.`id` ORDER BY t.`HAZBAHuE` ASC LIMIT 0, 1000
    Базу уже привёл в 1 посте. Как заставить работать быстрее? При создании базы происходит: " GROUP BY `id` ORDER BY v.`name` ASC" - это записывает в базу SQLite, в которой я копаюсь(Таблицы в 1 посте), и из неё пытаюсь выводить уже у себя локально данные. Т.е. пытаюсь ускорить максимально. Какие-нибудь предложения есть?
     
  5. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    155
    Симпатии:
    101
    Специфику ускорения запросов под SQLite не знаю.
    Я бы посмотрел как профилировать запросы.
    Как работать с планами запросов "EXPLAIN QUERY PLAN ваш-SQL-запрос" и "EXPLAIN ваш-SQL-запрос".

    Еще вопрос - почему SQLite? Это "урезанная" РСУБД. Под MySQL я бы подсказал.
     
    papa нравится это.
  6. papa

    papa Постоялец

    Регистр.:
    28 сен 2006
    Сообщения:
    148
    Симпатии:
    3
    Идея: сайт. Его база данных(в данном случае MariaDB, но это неважно) перегоняется в SQLite файл(php скрипт). Он то и должен открываться везде, где можно. Собственно: SQLite - переносная. В прогу(C#) встраивается dll-а для работы с ней(стандартная). Сначала, раньше было CSV файл. Но потом возникли проблемы(объёмы данных) -> SQLite показалась подходящей(я не спец. Рассмотрел - показалось - перекрестился - сделал).

    Тогда такой вопрос: у меня есть список категорий, из которых я хочу получить записи(Порядок их важен. По алфавиту, а не по id). Для примера:
    SELECT t.`id`,
    t.`HAZBAHuE`,
    t.`MODELb`,
    t.`KPATHOCTb`,
    t.`DOCTynHIOCTb`,
    t.`KAPTuHKA`,
    t_k.`KATEGOPuia`,
    t.`KOLOHKA1239`,
    t.`KOLOHKA1267`,
    t.`KOLOHKA1272`
    FROM
    `ZAnuCu_OnuCAHuE` t,
    `ZAnuCu_KATEGOPuu` t_k
    WHERE t_k.`KATEGOPuia` IN (64, 2680 ... 'и так 579 категорий') AND t_k.`id` = t.`id` ORDER BY t.`HAZBAHuE` ASC LIMIT 0, 1000
    . Так вот. Я добился: чтобы товары шли без сортировки в алфавитном порядке(сразу в базе. Добавил в "ZAnuCu_KATEGOPuu" поле "Название товара" и его отсортировал на входе. Мне нужен алфавитный порядок в категориях, а не в таблице товаров. Т.е. у меня есть список категорий(отсортированный, не по id) и есть отсортированный список товаров(по названию)).


    А как мне сделать так, чтобы было: из категории "64 дай мне всё что есть, если меньше 1000 записей", "2680 дай мне всё что есть, если меньше 1000 записей" и так далее, но чтобы при достижении сумарного количества записей "в 1000 едениц" оно мне выдавало результат(прилагая ему список категорий.)?
     
    Последнее редактирование модератором: 6 окт 2017
  7. zabolots

    zabolots Постоялец

    Регистр.:
    11 сен 2012
    Сообщения:
    56
    Симпатии:
    21
    Ну и названия полей у вас :)
    То есть задача такая: получить список товаров только из тех категорий, в которых этих товаров менее 1000 штук, верно? Первым запросом определяете id этих категорий, вторым выбираете записи.
    Примерно так: select * from opisanie where category_id IN (select category_id from opisanie group by category_id having count(*)<1000) - это на MySQL так, наверное и в sqlite так же.
     
    papa и Tiox нравится это.
  8. papa

    papa Постоялец

    Регистр.:
    28 сен 2006
    Сообщения:
    148
    Симпатии:
    3
    В общем вопрос решился таким запросом: индексация по колонкам, которым надо проводить сортировку; Колонки таблицы в количестве 10-20 штук; Не использовать программу "DB Browser for SQLite", т.к. в ней запрос выполняется больше по времени раз в 10.(при сортировке); и поиграться с настройками SQLite при соединении(соединились с базой, отправили настройки - после запрос.)

    В общем тестовый запорс в проге выполняется не более 0.5 секунды. А в "DB Browser for SQLite" - 6-7 секунд
     
  9. Rammstein_91

    Rammstein_91 Создатель

    Регистр.:
    30 июн 2015
    Сообщения:
    9
    Симпатии:
    0
    Присоединяясь к Black Hat, хотел бы всё таки уточнить - а почему базу не повесить в облаке на мощном сервере? Или задача подразумевает офлайн подключение с компов, не имеющих доступ в инет? Хотя сейчас это редкость, а в случае если доступ в инет закрыт (только локалка), то в файрволе/сервере просто открывается определенный порт для доступа к базе.