HoneyHoney

Python 和 C 一样，有形参和实参的概念。但是我用ruby的时候，却没有这样的感觉。我每次都会data = function(data)，这样的话，我就不需要去担心到底是传递值还是传递指针。但是，就Ruby而言，传递的是对象的引用。详情可以参考：http://www.iteye.com/topic/1117575

今天发现个新问题。发现在循环中声明变量并不会reset 变量值。

DECLARE @i INT = 0;

WHILE @i < 10
BEGIN
    DECLARE @b int

    print CONCAT('PREV:', @b)

    SET @b = @i

    PRINT CONCAT('NOW:', @b)

    SET @i = @i+1

END

执行上面的query，你会发现PREV的输出并不一致是空，而是有值的。顺手搜了一下，发现很多人都发现这个问题，但是却没有一个官方文档解释这个。立即创建一个规则去避免这坑。

Antlr的全程是ANother Tool for Language Recognition，可以用来实现编程语言的解析。由于项目的需要，最近一个月和同事完成了一个针对TSQL的语法分析，进而创建一些规则去审查代码（连这都自动化掉，我实在是太懒了！）。
Antlr是一个比较老的项目了，最新的版本是4.7。个人觉得，这个项目现在之所以成功，还是因为开源以后，很多人都来贡献Antlr的语法文件。在github上，你可以找到主流编程语言的语法文件（https://github.com/antlr/grammars-v4/）。有了语法文件，你就可以直接通过简单的教程使用Antlr。
我们在使用Antlr的时候，已经有了TSQL.g4文件了。语法不是很完整，但我们可以自己贡献代码去帮助其更加完整。可以预见的是，这些语法文件会越来越完整的。

学习Antlr

最经典的还是这个工具开发者自己写的书《The Definitive ANTLR 4 Reference》。快速入门，可以参考https://tomassetti.me/antlr-mega-tutorial/#working-with-a-listener 以及 http://jakubdziworski.github.io/java/2016/04/01/antlr_visitor_vs_listener.html
此外，中文的可以参考https://dohkoos.gitbooks.io/antlr4-short-course/content/getting-started.html。

分析TSQL

这个项目的目的就是分析TSQL，进而审查代码，以便开发避免一些坏的编程习惯。要分析TSQL，我们第一反应都是用正则表达式。很不幸，在这个事情上，正则表达式简直是弱爆了！我找到一个比较成熟的语法分析工具，不过是针对pg数据库的。https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html。文中作者在无数次尝试以后，也发现正则表达式的无力。
通常，我们会看微软自己有没有开发的工具或者API用来分析TSQL。最好的情况就是SSMS里面有个语法分析的组件单独分离出来使用。其实后来我也找到了这样的工具，叫 microsoft.sqlserver.transactsql.scriptdom。但是后来我们考虑到可能还要支持其他类型的数据库，所以我还是选择了Antlr。
在Github上面找到tsql的语法文件，再按照快速入门的步骤，配置好环境，执行下面的命令，就可以拿到整个语法树了。

antlr4 tsql.g4 && javac *.java && grun tsql tsql_file -gui test.sql

Vistior 和 Listener之争

这只是分析语法的两种不同方式而已，看自己的情况选择。一般来说，如果你只想分析某一个rule以及他下面的rule，可以考虑用Visitor。用Listener的话，可以自动的遍历所有的rule。个人而言，还是喜欢Listener。
比如，我们要求在存储过程中，不能够使用PRINT语句，因为我们不希望存储过程返回除了respond code之外的信息。用Listener的话，我只需要在进入和退出存储过程的时候，设置一个flag。然后再判断所有的PRINT语句，如果当前flag为true，就表明这个PRINT是在存储过程里面。

计算某列的和，可以直接用SUM。但如果和SIGN结合起来，事情就有可能复杂了。比如，SUM可以替换成下面一行

SUM((1 - SIGN(Amount)) * Amount / 2) + SUM((1 + SIGN(Amount)) * Amount / 2)

其中，前一部分是算所有正数和，比CASE WHEN 巧妙。

SELECT SUM((1 + SIGN(Amount)) * Amount / 2)

后一部分是算负数和的绝对值。

SELECT SUM((1 - SIGN(Amount)) * Amount / 2)

巧妙+巧妙却不一定等于巧妙！

此外，还有求绝对值的和

SUM((1 + SIGN(Amount)) * Amount / 2) - SUM((1 - SIGN(Amount)) * Amount / 2)

如果要计算昨天的时间，第一反应都是

SELECT DATEADD(DAY, -1, GETUTCDATE())

但还有另外一种写法。因为SQL SERVER的base date是1900-01-01，所以，你可以试着写成下面这样，同样可以得到昨天。

SELECT GETUTCDATE() + '1899-12-31'

可能时间长了，大家会免疫这种写法，你还可以改成，1899-12-30，即-2天。

SELECT DATEADD(DAY, 1, GETUTCDATE()) + '1899-12-30'

还可以这样

SELECT GETUTCDATE() - 1

有这么一个需求，查某个customer在某个时间段内是否买个产品。一般情况下，都会用JOIN，然后再去重即可。比如

SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849

这样做有个问题，如果customer对应的orders比较多的时候，IO开销就不一样了。因为执行计划会每次读取所有的orders，然后再去重。

相反，如果用EXISTS，执行计划就会去判断是否买过产品，买过即停止，不会继续扫描。

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849

对比IO开销，前者比后者开销更多。在复杂query和数据量比较多的情况下，这种差距更加明显。

此外，如果query一直这么简单，那么SQL SERVER会自动优化，会在执行计划中加入TOP操作，这样就不会把所有记录都查出来再去重。

所以，尽所有可能，把EXISTS都替换成JOIN，增加IO开销吧！

use master;

IF NOT EXISTS(SELECT 1 FROM SYS.DATABASES WHERE NAME = 'test_db')
    CREATE DATABASE test_db;
GO

use test_db;
GO


IF OBJECT_ID(N'customers') IS NOT NULL
    DROP TABLE customers;

CREATE TABLE customers (
    id int,
    CONSTRAINT pk_customers PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO customers(id)
SELECT n
FROM dbo.getnums(500000)


IF OBJECT_ID(N'orders') IS NOT NULL
    DROP TABLE orders;

CREATE TABLE orders (
    id int identity(1, 1),
    customer_id int,
    order_dt datetime,
    CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (ID),
    CONSTRAINT fk_orders__customer_id FOREIGN KEY (customer_id) REFERENCES customers(id)
)


INSERT INTO orders(customer_id, order_dt)
SELECT n/100+1, DATEADD(DAY, n/700, '2000-01-01')
FROM dbo.getnums(5000000)

INSERT INTO orders(customer_id, order_dt)
SELECT 43849, DATEADD(DAY, n/70, '2000-01-01')
FROM dbo.getnums(500)


SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849
OPTION(RECOMPILE, MAXDOP 1)

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849
OPTION(MAXDOP 1)

背景是，有两个report的金额不平，需要找出里面不平的原因。很早以前是用这个工具去实现的。
新建一个excel文件，在文件里A、B栏输入report A的key和value，在D、E栏输入report B的key和value，然后执行下面的命令就行了。



代码

require 'slop'
require 'logger'
require 'simple_xlsx_reader'


# Usage
# ruby discrepancy_finder.rb [options]
# options:
# -v print the version
# -d enable debug mode
# -m model:
#     model = 1 check both keys and values
#     model = 2 only check keys
# -s sheet index: 0 will be the first sheet
# example:
# ruby discrepancy_finder.rb
# ruby discrepancy_finder.rb -d
# ruby discrepancy_finder.rb -m 2
# ruby discrepancy_finder.rb -d -m 2 -s 0


opts = Slop.parse do |o|
  o.integer '-m', '--model', 'model', default: 1
  o.integer '-s', '--sheet_index', 'specific sheet number', default: 0
  o.bool '-d', '--debug', 'enable debug mode'
  o.on '-v', 'print the version' do
    puts '0.0.1'
    exit
  end
end

def logger
  @logger ||= Logger.new(STDOUT)
end

def load_col_data(wb, col_index)

  _hash = Hash.new

  lastrow = 1

  while wb.rows[lastrow] && wb.rows[lastrow][col_index]

      _hash["#{wb.rows[lastrow][col_index]}"] = (_hash["#{wb.rows[lastrow][col_index]}"].nil? ? 0 : _hash["#{wb.rows[lastrow][col_index]}"]) + ( wb.rows[lastrow][col_index+1].nil? ? 0 : wb.rows[lastrow][col_index+1].to_f.round(2) )
      lastrow = lastrow + 1
  end

  _hash

end

doc = SimpleXlsxReader.open("revenue_report_diff.xlsx")

wb = doc.sheets[opts[:sheet_index]]

logger.debug "Running in model #{opts[:model]}" if opts.debug?

#remove duplicate records
left_hash = load_col_data(wb, 0)
right_hash = load_col_data(wb, 3)


leftSum = left_hash.values.inject(:+).round(2)
rightSum = right_hash.values.inject(:+).round(2)


revenueDiff = leftSum - rightSum
puts '##########REVENUE DIFF(LEFT - RIGHT)##############'
if opts[:model] == 2
  puts "LEFT: " + leftSum.to_s
  puts "RIGHT: " + rightSum.to_s
  puts "DIFF: " + revenueDiff.round(2).to_s
end

lostRecordsInRight =  left_hash.keys - right_hash.keys
lostRecordsInLeft = right_hash.keys - left_hash.keys

unless lostRecordsInRight.empty?
  puts 'Missing records in RIGHT side:'
  lostRecordsInRight.each do |t|
      puts t + "\t" + left_hash["#{t}"].round(2).to_s
  end
end

unless lostRecordsInLeft.empty?
  puts 'Missing records in LEFT side:'
  lostRecordsInLeft.each do |t|
      puts t + "\t" + right_hash["#{t}"].round(2).to_s
  end
end

if opts[:model] == 2
  puts 'Discrepancy:'
  left_hash.each do |k, v|
      if right_hash["#{k}"].nil?
        # puts k + " is missing."
      elsif left_hash["#{k}"].round(2) != right_hash["#{k}"].round(2)
          puts k + "\t" + left_hash["#{k}"].round(2).to_s + "\t" + right_hash["#{k}"].round(2).to_s
      end
  end
end