Category Archives: db

Protected: pg_restore

This content is password protected. To view it please enter your password below:

mysql example

  • Сколько M и W в каждом департаменте
  • Максимальное количество в одном департаменте

 

# id, dep, s
'1', '1', 'm'
'2', '1', 'm'
'3', '1', 'w'
'4', '2', 'm'
'5', '3', 'm'

 

 

select *
from (
 SELECT dep, count(dep) as c ,s FROM vn_test.new_vn_test  where s ='w'   group by dep  -- HAVING c = "m" 
    UNION   ALL
 SELECT dep, count(dep) as c ,s FROM vn_test.new_vn_test  where s ='m'  group by dep --  HAVING c = "m"  ;
) as a
order by dep asc

 

  
select if (1>2,"a","r"); 
SELECT REPLACE("ABC ABC ABC", "A", "B"); 
 #> 'BBC BBC BBC'

 

 

 

 

 

select dep, count(case when s=’m’ then 1 else NULL end), count(case when s=’w’ then 1 else NULL end) FROM table GROUP BY dep

select dep, cnt FROM (select dep, count(1) cnt FROM table GROUP by dep ORDER by cnt DESC) p LIMIT 1

 

Справочное руководство по MySQL

Справочное руководство по MySQL

6.3.1.4 Функции потока управления программой

6.3.1.4 Функции потока управления программой

IFNULL(expr1,expr2)
Если expr1 не равно NULL, то функция IFNULL() возвращает значение expr1, в противном случае – expr2. В зависимости от контекста функция IFNULL() может возвращать либо числовое, либо строковое значение:

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'
NULLIF(expr1,expr2)
Если выражение expr1 = expr2 истинно, то возвращает NULL, в противном случае – expr1. Эквивалентна оператору CASE WHEN x = y THEN NULL ELSE x END:

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1

Отметим, что если аргументы равны, то величина expr1 вычисляется в MySQL дважды.

IF(expr1,expr2,expr3)
Если expr1 равно значению ИСТИНА (expr1 <> 0 и expr1 <> NULL), то функция IF() возвращает expr2, в противном случае – expr3. В зависимости от контекста функция IF() может возвращать либо числовое, либо строковое значение:

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

expr1 вычисляется как целое число; это означает, что при исследовании чисел с плавающей точкой или строковых величин в этой функции необходимо использовать операцию сравнения:

mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
        -> 1

В первом случае из приведенных выше функция IF(0.1) возвращает 0, так как 0.1 преобразуется в целое число и в результате выполняется функция IF(0). Но это вовсе не то, что должно было бы получиться. Во втором случае исходная величина с плавающей точкой исследуется при помощи оператора сравнения, чтобы определить, является ли она ненулевой, и в качестве аргумента функции используется результат сравнения – целое число. В версии MySQL 3.23 возвращаемый по умолчанию тип функции IF() (это может иметь значение при сохранении его во временной таблице) вычисляется, как показано ниже:

Выражение Возвращаемая величина
expr2 или expr3 возвращает строку строка
expr2 or expr3 возвращает величину с плавающей точкой с плавающей точкой
expr2 or expr3 возвращает целое число целое число

Если expr2 и expr3 являются строками, и обе зависимы от регистра символов, то результат является чувствительным к регистру (начиная с 3.23.51).

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
В первом варианте возвращается значение result, если value=compare-value. Во втором – результат для первого указанного условия condition, если оно истинно. Если соответствующая величина результата не определена, то возвращается значение result, указанное после оператора ELSE. Если часть ELSE в выражении отсутствует, возвращается NULL:

mysql> SELECT CASE 1 WHEN 1 THEN "one"
           WHEN 2 THEN "two" ELSE "more" END;
       -> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
       -> "true"
mysql> SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END;
       -> NULL

Тип возвращаемой величины будет такой же (INTEGER, DOUBLE или STRING), как и у первой возвращаемой величины (выражение после первого оператора THEN).

Sphinx Search

В этой статье я опишу весь путь от установки до использования системы полнотекстового поиска Sphinx.

Для наглядности примера, пускай у нас есть таблица с улицами и нам нужно делать поиск по ней. Понимаю что улиц не так много, вместо них могут быть телефоны или что-то подобное, с большим количеством записей.

 

 

 

01. Установка и конфигурирование sphinx

Ради sphinx’а пересел обратно в ubuntu и, что удивило, помоему убунту стала намного стабильнее. Наверное даже не сама система, сколько приложения, которыми я пользуюсь, пока что и не собираюсь возвращаться на винду(кс – есть, а это главное :)).

Установка занимает ровно 1 команду:

sudo apt-get install sphinxsearch

Грубо говоря сфинкс парсит данные напрямую из mySQL, в конфиге сфинкса указываем SQL запрос, который и будет потом индексирован. Вообще сам конфиг делится на 3 части: источники данных, параметры индексации и параметры самого сервера. Суть следующая индекс включает в себя один или несколько источников данных и путь до места, где этот индекс хранится. Если источников несколько, то они должны быть однородны (одинаковое количество столбцов). После обработки поискового запроса, sphinx возвращает id найденных записей. Соответственно в источнике нужно указать какое поле и будет браться как id. Причем их может быть несколько.

Перейдем непосредственно к примеру: у нас есть таблица street, содержащая id и street_name. Теперь нам нужно отконфигурировать сам сфинкс, для этого будем править файл /etc/sphinxsearch/sphinx.conf.

Конфиг для источника данных:

source street_name {
    type = mysql
    sql_host = 127.0.0.1
    sql_user = root
    sql_pass =
    sql_db = test
    sql_port = 3306
    sql_query_pre = SET NAMES utf8
    sql_query = SELECT id, street_name FROM street
    sql_attr_uint = id
    sql_query_info = SELECT street_name FROM street WHERE id=$id  
}

Здесь sql_query – это тот самый запрос, который будет проиндексирован, а sql_query_info – это вспомогательный запрос, который будет показываться, ну например при тестировании работы сфинкса из консоли.

Конфиг для индекса:

index street {
   source = street_name
   path = /var/lib/sphinxsearch/data/street
   morphology = stem_ru
   min_word_len = 1
   charset_type = utf-8
 }

Заметьте, что в качестве источника мы используем street_name – имя источника. Далее идет путь до места, где будет храниться индекс и прочие настройки.

И последние, в этом же файле должен быть конфиг для самого процесса сфинкс:

searchd {
   listen = 127.0.0.1:9306:mysql41
   log = /var/log/sphinxsearch/searchd.log
   query_log = /var/log/sphinxsearch/query.log
   pid_file = /var/run/sphinxsearch/searchd.pid
   read_timeout = 5
   max_children = 30
   max_matches = 1000
   seamless_rotate = 1
   preopen_indexes = 0
   unlink_old = 1
 }

Описывать подробно его не буду – все параметры можно посмотреть на сайте сфинкса.

02. Индексирование

Для первоначального индекса в консоли запустим команду:

sudo indexer street

Ниже – результаты:

 Copyright (c) 2001-2012, Andrew Aksyonoff
 Copyright (c) 2008-2012, Sphinx Technologies Inc (   http://sphinxsearch.com)
 using config file '/etc/sphinxsearch/sphinx.conf'...
 indexing index 'street'...
 WARNING: attribute 'id' not found - IGNORING
 WARNING: Attribute count is 0: switching to none docinfo
 WARNING: collect_hits: mem_limit=0 kb too low, increasing to 12288 kb
 collected 3402 docs, 0.1 MB
 sorted 0.0 Mhits, 100.0% done
 total 3402 docs, 129590 bytes
 total 0.054 sec, 2380462 bytes/sec, 62491.96 docs/sec
 total 2 reads, 0.000 sec, 24.5 kb/call avg, 0.0 msec/call avg
 total 6 writes, 0.000 sec, 17.5 kb/call avg, 0.0 msec/call avg

Видно, что проиндексировал 3402 записей и теперь также протестируем в консоли индекс:

search -i street Ленина

Выбираем в качестве индекса street и по ней делаем поиск по слову Ленина. Я получил id двух записей – 2891 и 2892. Это id совпавших записей в нашей таблице.

Второе что нужно знать – в случае если у нас добавятся улицы и нужно будет переиндексировать таблицу, используем команду:

sudo indexer --rotate --all

03. Shpinx и Yii framework

Теперь, когда у нас есть индекс, нужно подключить сфинкс к yii. Можно использовать расширение, но мне оно сразу не понравилось, вылезло кучу ошибок, какие-то эксепшены, поэтому я воспользовался рецептом Загирова Рустама – http://www.zagirov.name/yii-cookbook-2 и подключил сфинкс как компонент:

1
2
3
4
'sphinx' => array(
 'class' => 'system.db.CDbConnection',
 'connectionString' => 'mysql:host=127.0.0.1;port=9306',
),

Далее просто создаем запрос на выборку данных, аналогичино работы с MySQL:

1
$sSql = 'SELECT id FROM street WHERE MATCH(' . Yii::app()->sphinx->quoteValue($term) . ')';

И выполняем запрос:

1
$ids = Yii::app()->sphinx->createCommand($sSql)->queryAll();

Теперь $ids содержит массив с id улиц. Далее конкретные названия, если они нужны, можно получить, используя обычный AR.