Executando verificação de segurança...
Em resposta a Desvendando o Algoritmo de Ranqueamento do Tabnews
3
NullPointerDeception

Na minha opinião a implementação é muito elegante, inteligente e eficiente, ao mesmo tempo que é bastante simples. E mostra como SQL é uma ferramenta extramamente poderosa.

Eu não sei se concordo. E digo "não sei" porque o meu raso conhecimento de SQL não me permite ter uma opinião muito embasada a respeito. Não me parece elegante uma query de quase duas centenas de linhas chumbada no código como string, e com esse monte de JOINS, UNIONS etc também fico me perguntando se é realmente eficiente como você citou ou se essa regra de negócio poderia ter sido quebrada pra melhorar a legibilidade, manutenabilidade e até performance (o que talvez dependesse de outra modelagem do banco).

Eu posso estar (e provavelmente estou) enganado a esse respeito, e gostaria muito que alguém discorresse a respeito dessa abordagem que o Filipe escolheu. Melhor ainda se fosse o próprio Filipe. Não é crítica, são dúvidas honestas de alguém que quer aprender.

Carregando publicação patrocinada...
1
balavalda

Jois fazem parte do SQL não há como fugir, apenas tens falta de familiaridade com eles. A abordagem de escrita é sim boa, já que, ele segregou varias partes em CTE. Se fosse mal feito seria uma série de subselects dentro da query principal. Por exemplo, ao ler a query temos:
latest_published_child_contents: Uma CTE que seleciona os IDs e caminhos dos conteúdos mais recentes que foram publicados nas últimas 24 horas e têm um parent_id não nulo (ou seja, são filhos de outros conteúdos).
latest_interacted_root_contents:

  • Uma CTE que combina duas seleções
    • A primeira parte seleciona os conteúdos raiz (não têm parent_id) que foram publicados nas últimas 7 dias e têm um saldo positivo de "tabcoins"
    • A segunda parte seleciona os conteúdos raiz que estão associados aos conteúdos da CTE latest_published_child_contents, garantindo que não tenham o mesmo owner_id. Isso inclui novamente o cálculo de saldo de "tabcoins"
      ranked_published_root_contents: Uma CTE que seleciona conteúdos raiz da CTE latest_interacted_root_contents que possuem um saldo positivo de "tabcoins".
      Calcula um "score" para cada conteúdo com base em uma fórmula envolvendo o saldo de "tabcoins" e o número de proprietários únicos dos conteúdos que possuem uma relação hierárquica com o conteúdo atual.
      group_1 a group_5: CTEs que dividem os conteúdos da CTE ranked_published_root_contents em grupos com base em critérios específicos de tempo e pontuação. Os grupos são definidos por intervalos de tempo e pontuações mínimas.
      Cada grupo é ordenado e limitado a um número máximo de resultados.
      ranked
      Uma CTE final que une os resultados das CTEs group_1 a group_5 e os classifica novamente com base em um critério de classificação mais amplo.
      A consulta final também inclui informações detalhadas sobre os conteúdos, como IDs, proprietários, títulos, pontuações, etc.
      Os resultados finais são ordenados por grupo, pontuação e data de publicação.

Ou seja, se tu quebrar as partes fica bem fácil de entender, e na área em que trabalho (engenharia de dados/bi) a gente acaba precisando escrever querys muito mais complexas, então creio que seja familiaridade mesmo.

1
clacerda

Sim a implementação é bastante eficiente e isso pode ser medido de clara e empirica, é objetivo. Isso é um fato, assim como é um fato que qualquer implementacao deste mesmo algortimo usando JavaScript seria infinitamente mais lenta e complexa. Isso explica o motivo de ter uma query de 200 linhas jogados no meio de um sistema feito todo com um ORM de ultima geracao.

regra de negócio poderia ter sido quebrada pra melhorar a legibilidade, manutenabilidade

Isso comcerteza poderia ser, a query poderia por exemplo se utilizar de views e funcoes criadas no postgres abstrair parte da complexidade e aumentar a legibilidade e manutenabilidade, mas como tudo na computacao sao tradeoffs e com meu sql enferrujado consegui entender esta query em menos de 5 minutos, entao por isso acho que nenhum esforço foi feito nesse sentido.

1
NullPointerDeception
Autor
Autor

é um fato que qualquer implementacao deste mesmo algortimo usando JavaScript seria infinitamente mais lenta e complexa. Isso explica o motivo de ter uma query de 200 linhas jogados no meio de um sistema feito todo com um ORM de ultima geracao

Tem que ver até onde isso é real, caso a caso. Por exemplo, no desenvolvimento com C# é muito comum as pessoas pensarem que o Dapper por ser mais leve que o Entity Framework e ser comumente utilizado com RAW SQL no código é mais performático, mas isso não é uma regra, pois ORMs modernos podem pegar a sua query feita em métodos e escrever uma query realmente performática por baixo dos panos, muitas vezes mais do que uma query escrita "na unha" por um desenvolvedor. E isso na verdade não é nem uma opinião, é algo que já foi amplamente discutido e testado.

E claro, estamos falando de UMA query. Se o Tabnews for crescer e as queries forem ser sempre implementadas assim, não vejo como isso pode ser facilmente manutenível e nem escalável. É comum ver implementações assim em grandes projetos? Eu tenho contato com um grande projeto legado atualmente, de cerca de 500 mil linhas, e ele é repleto de queries chumbadas. Honestamente é um pesadelo em todos os sentidos.

1
clacerda

e as queries forem ser sempre implementadas assim, não vejo como isso pode ser facilmente manutenível e nem escalável. É comum ver implementações assim em grandes projetos? Eu tenho contato com um grande projeto legado atualmente, de cerca de 500 mil linhas, e ele é repleto de queries chumbadas. Honestamente é um pesadelo em todos os sentidos.

O que é comum - e recomendado - de se ver em grandes projetos são os chamados DAOs (data access objects) no lugar das queries chumbadas no código. São objetos que executam uma query, mas obdecem à uma interface bem construída. De certa forma são uma espécie de 'ORM' feitos sob medida para cada projeto.

Os ORMs são tão populares porque lidam eficientemente com a maioria dos casos - digamos 99% para ilustrar. No entanto, à medida que as consultas se tornam mais complexas, nos aproximamos desse 1% restante - melhor recorrer ao SQL puro.

Infelizmente, como você mencionou em muitos projetos grandes onde um ORM é usado, surge a necessidade do uso direto do SQL e - muitas vezes - o código acaba sendo implementado sem seguir nenhum padrão ou boa prática. Isso leva as tais queries chumbadas.

A solução ideal seria refatorar esses trechos conforme surgem utilizando o design pattern DAO ou até mesmo integrando diretamente no ORM utilizado quando este oferece recursos para isso.

Boa sorte em seu projeto!

0
clacerda

No desenvolvimento com C# é muito comum as pessoas pensarem que o Dapper por ser mais leve que o Entity Framework e ser comumente utilizado com RAW SQL no código é mais performático, mas isso não é uma regra, pois ORMs modernos podem pegar a sua query feita em métodos e escrever uma query realmente performática por baixo dos panos, muitas vezes mais do que uma query escrita "na unha" por um desenvolvedor. E isso na verdade não é nem uma opinião, é algo que já foi amplamente discutido e testado.

Sim. Defender que o SQL puro é virtualmente sempre mais rápido não é apenas uma opinião, mas um fato comprovado por várias pesquisas [1][2]. Este estudo de caso inclusive compara o dapper com o linq como você mencionou e bem ele realmente é bem mais rápido..

Embora os ORMs modernos sejam capazes de gerar consultas SQL eficientes, eles ainda adicionam uma camada extra de abstração que necessariamente vai levar a um desempenho ligeiramente inferior em comparação com o SQL puro. Em um caso que ambos tenham sido 100% otimizados, o SQL sempre vai ser rápido, simples.

Em relação a otimização feita "na unha" X otimização feita pelo ORM: No PostgreSQL, por exemplo, você pode usar o comando EXPLAIN para entender exatamente como sua consulta está sendo executada. O EXPLAIN retorna um plano de execução detalhado que mostra o caminho escolhido pelo otimizador do PostgreSQL para resolver a consulta a nível de acessos de disco. Isso permite identificar gargalos potenciais e obter o melhor desempenho possível. De maneira que seria simplemente impossivel no nível de abstração de um ORM. Utilizando consultas como EXPLAIN e ANALYZE da maneira correta, um DBA SEMPRE vai ser capaz de escrever uma query mais eficiente que qualquer ORM faria.

1
clacerda

gostaria muito que alguém discorresse a respeito dessa abordagem que o Filipe escolheu. Melhor ainda se fosse o próprio Filipe. Não é crítica, são dúvidas honestas de alguém que quer aprender.

Obrigado por seus questionamentos inteligentes, não posso responder pelo Filipe, porque essa abordagem foi escolhida, mas espero que tenha esclarecido suas dúvias e acredito que os links referencidos em minhas outras respostas são um execelente material para quem quer aprender.

Entenda o desempenho do sua query
Todo o poder dos dados em suas aplicações